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!!!