Left() like in excel

[funkykayli]

I have a query in which I want to find duplicates. I can not use the duplicate query because:

I have a list of claim numbers. Here is an example of duplicates I need to identify:
12345A
12345B
12345C
So the duplicate query won't work because they are actually different. Is there a way to look at just the first 5 characters for duplicates?

 

[Joe4]

You are on that right track.

One way is to create an "intermedidate query", which use the LEFT function to take the first five characters of your field. Then use that query in a new query to find the duplicates (can be done using an Aggregate Query).

 

[funkykayli]

I'm sorry but what do you mean by an intermediate query? Is that just a separate query to use the left function? Also, where would I put the left function? Do I put that in the sql statement? Sorry for all the questions but I am a newbie with Access.

 

[funkykayli]

Ok, I think I figured it out. I was putting the expression in the criteria instead of the field.

Thank you!!!!!!!

 

[funkykayli]

Ok, I got the left expression to work in a query. Now when I go to run a duplicate query on the field I created with the left expression I get an error message...

"You tried to executre a query that does not include the specified expression 'count(*)>1 And Left([CLM_ID],13)=' as part of an aggregate function."

I do not understand this error message. Is there a way around it?

 

[Joe4]

That is why I was recommedning an intermediate query. Access gets very tempermental when you try to do aggregate queryies on fields calcaulted in the same query.

So, create a Query "A", that does your calculations
Then, create a second Query "B", which uses the Query "A" as its control source and does the Aggreagte Query (duplicates).