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

jerryb30

Try VAL(your string)

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

Francis Omoruto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

harfang

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

harfang

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

smithmrk

ASKER

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

Thanks!
Mark

Francis Omoruto

You're welcome!
Franc'O.