less than not working for numeric values with a decimal

[Maverick28]

Hi all,



In a query I am using an Iif. The expression reads if a value in a column is <3 then "Y","N".

This works perfectly fine except for on lines where there is 3.9 for example, where it will return Y.

How can I get around this? In Excel it works fine.

TIA!

[Bob Fitz]

I suspect that your column field is of type Text, in which case it would need to be converted to a number. Try:

Result: IIf(CSng([YourColumnField])<3,"Y","N")

[Maverick28]

Gave this a try, but unfortunately didn`t work. I have checked the format of the field and it`s Number. I was trying to do a calculation in the if, in the same way I would do it in Excel. It looks like I will have to do in multiple steps...

[pbaldy]

Perhap you should show your actual formula. I had the same thought Bob did, though even text would evaluate 3.9 as greater than 3. Unless I've had too much wine.