Question

Microsoft Access

Microsoft Access

I have created an Iff statement where it will compare two fields and return the bigger number. The problem is that instead of compare the numbers as a whole, it compares them digit for digit. Result is that it will return a value of 150 instead of the bigger value of 1040, because the second digit in 150 is 5 and in 1040 is 0. How can I fix this to read the numbers as a whole?

Hello Jaco,

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...

ID Result
2 1040
3 that
4 This

Note that the result is still of "text" data type.


Thank you,
Jaco