iif Function issue

[scgoodman]

I am trying to do a iif function in a query. I have two numbers that I have formatted ([Name], "General Number") and with these two numbers I am getting invalid results.

Little history:

1. Number is Actual Days in Transit
2. Number is Commit Time

I am doing an iif([Actual Days in Transit] <=[Commit Time], "Ontime","Late")

Some records come back ok showing perfect ontime, however I have some records that are not. Suggestions?

 

[pbaldy]

What are the data types of the two fields in the table? What is an example of what they contain when the expression doesn't work?

 

[scgoodman]

I formatted them to general number using format([Actual Days in transit],'General Number') same with Commit Times.

 

[pbaldy]

Well, my question related to the actual data type, not the format. That said, the Format() function will return a string, so your comparison will have undesired results.

 

[scgoodman]

Do you have any suggestions?

 

[pbaldy]

You could tell me what the actual data types are, and try not using the Format() function.