Query returns only certain results

[Moss100]

Hello I have a very simply query which returns results for currency amounts

The query is: Like LCase('*' & ([Type Search Criteria]) & '*')

If i type in 97.5 I get returned results, but if I type in 97.50 I do not get results.

The field tyupe is currency and there are records £97.50 in there.

Thanks Mark

 

[Golom]

LIKE compares character strings, not numbers. To do the comparison VB has to internally cast your currency value to a string and it drops the trailing zero. Hence 97.5 gets a match but 97.50 does not. You should probably use an equal(=) comparison operator rather than LIKE.

[blue]CurrencyField = [Type Search Criteria][/blue]

 

[Moss100]

Will this still return results that are not exact - ie if i search for 97.50, I would like it to bring up results such as 197.50 etc... Thanks Mark

 

[Golom]

No. The equals operator (=) does an exact match. If the values are meaningful only to two places after the decimal then you can manually do the conversion of number to string and use LIKE. Something like this

Format(CurrencyField, "#.00") LIKE "*" & [Type Search Criteria] & "*"