to find same digits or letters in different order

[Harrold]

Hi

Say i want to find an amount of $659 or a word 'Christine'. I suspect the amount or word may key in wrong, eg 659 becomes 569. In query, what can i put in criteria to search for the digits or words in different order?

Thanks

 

[plog]

You'd either have to make a WHERE statement that explicitly states all the permutations to search for:

WHERE YourFieldNameHere="Christine" OR YourFieldNameHere="Christien" OR YourFieldNameHere="Christein" OR...

Or you could use a LIKE and check to make sure every character appears in some order in the field:

WHERE YourFieldNameHere LIKE "*C*" AND YourFieldNameHere LIKE "*h*" AND YourFieldNameHere LIKE "*r*" AND ...

Of course if they entered "Christina" instead of "Christine" your hosed either way.

 

[Harrold]

Thanks Plog,

I used your second method. But all the figures contain 6, 5 and 9 are shown, ie 6590, 15691 etc.

How can i only have 3 digits?

 

[plog]

If its a numeric field then add a condition on there that the value has to be less than 1000, if its a string then make a condition that the length of it has to be 3 characters.

 

[Harrold]

Thanks Plog,

sorry to trouble you again. How about 9986? when i set the criteria as "*9*","*8*","*6*" , again 9860 also come out.

Would you please provide me with solution?

Thanks again

 

[plog]

You can add Not Like statements to exclude numbers as well

YourFieldNameHere LIKE "*9*" AND YourFieldNameHere LIKE "*8*" AND YourFieldNameHere NOT LIKE "*7*" AND YourFieldNameHere LIKE "*6*" AND YourFieldNameHere NOT LIKE "*5*" AND ...

But in all honesty, you're going to get false positives (9686, 9689, etc). And this method only checks for transposed numbers, what if they miskey 6 and put 3? Or completely omit the 4th digit of a 4 digit number? The real question is how do you know something has been mistyped? Is there data you're checking it against?