Microsoft Access

65 pts.
Tags:
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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • YuvalShavit
    In a nutshell: use CInt (or CLng) to convert the text to numbers before you compare them. When you do that, make sure to call IsNumeric first, as VBA's conversion functions do not error out gracefully (they throw an error instead of just returning Empty).
    905 pointsBadges:
    report
  • Ahafez
    Hi Jaco Your 2 fields are text You may try this : iif(Val(field1)> val(field2);field1;field2)
    60 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following