Link to home
Get AccessLog in
Avatar of smithmrk
smithmrkFlag for United States of America

asked on

Check for Duplicates

OK, I need some help with this one!

I have a Query that reformats the data so it can truly look for duplicate items that may have been keyed differently.

For example if someone types in 01256 and someone else put in 1256 it doesn't come up as a duplicate, so I have a query with an Expression that reformats the field to 00001256, puts 20 leading zeros so the number comes out exactly the same no matter how the data was entered.

Now I tried to do a Find Duplicates Query based on this field and I'm getting (see image).

If I put the same format expression in the IN SELECT() then I get overflow error.

How can I get around this without chaning the field to Numeric it needs to stay TEXT!!!

Thanks,
Mark
FieldError.jpg
Avatar of jerryb30
jerryb30
Flag of United States of America image

Try VAL(your string)
Avatar of Francis Omoruto
Post the full query please.
All non-aggregate items in the select part of the query must appear in the "group by" clause.
:(
ASKER CERTIFIED SOLUTION
Avatar of Francis Omoruto
Francis Omoruto
Flag of Uganda image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
When you use the Format() function, it will first convert your text to number (*overflow*) before attempting the number formatting you require. Try this solution to left-pad your numbers with zeroes:

PaddedAcct: Right(String(20,'0') & [Items].[Account],20)
Good luck!
(°v°)
SOLUTION
Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of smithmrk

ASKER

I want to thank everyone for your wonderful suggestions!
I'm on vacation tomorrow, but I will apply some of your ideas on Friday when I'm back in the office, and let everyone know what I find or figure out.

Thanks,
Mark
The Format Function was exactly want I needed!!!!
I was trying to do to much wiht CLng and adding / subtracting Lenghts, etc.

Thanks!
Mark
You're welcome!
Franc'O.