Your iif statement probably looks something like this..
Result: IIf([Field1] > [Field2] ,[Field1],Field2)
Assuming that Field 1 is a data type of Text and contains the value “150”, Field2 is also text data type and contains the value of “1040”, The above expression will return 150 as Result because a text comparison is made as opposed to using a numeric data type.
The solution then is to convert both field1 and field2 to a numberic data type before comparing. It can be done this way..
Result:iif(IIf(isnumeric([Field1]), clng([field1]), [Field1]) > IIf(isnumeric([Field2]), clng([Field2]), [Field2]),[Field1], [Field2])
The above method will work for text fields that contain long integers (whole numbers) as well as handle any true text values that may be contained in field1 and field2. For the data below…..
ID Field1 Field2
2 150 1040
3 150 that
4 This 1040
The result from this expression is…
Note that the result is still of “text” data type.