Link to home
Create AccountLog in
Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America

asked on

Iif statement needs to account for an or statement value - syntax in Microsoft Access

I'm still having trouble with IIF statements and their syntax:

I got this far with this:

IIF(IsNull([JobTicket]), "0" , "1")

Open in new window


The only thing it needs to do if it is Null or 0 then provide the value 0.
Right now it only accounts for Null

JobTicket is a numeric value but I'm providing the strings as text cause it's a field design to do sorting on.
If it should go back to numeric instead then I'd put it back.  But does that change the syntax as well?

Thank you in advance for your comments!
Avatar of bfuchs
bfuchs
Flag of United States of America image

you don't need IsNull for this, use NZ([JobTicket],0)
Avatar of stephenlecomptejr

ASKER

My question is regarding IIF
Based on the numeric values, I'm sorting so that any 0 or null values get sorted last versus the rest of the values that would have a numeric value between 1-2000.
for that you dont need to use conversion at all, just sort by JobTicket column.
Then you might try:

SortBy:  IIF(NZ([JobTicket], 0) = 0 , 3000, [JobTicket])
Sort: Ascending
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you sir.  I appreciate you answering my question.
Just to add a bit, to sort a numeric value in a text (string) field, then you need to format it with the same number of digits.  i.e.:

"0001"
"1000"

if you leave it like:

"1"
"1000"

 It's not going to sort right.   It will sort correctly without padding if it is numeric.

 Padding is achieved with format()

 Format$(<value,"0000")

 The problem with doing this is you always need to make sure your padding with more digits than your largest number.

HTH,
Jim.
If you are dealing with only numbers as text (nothing but number characters), then you can easily convert to a number data type and that solves the sorting issue:

Val(NZ([JobTicket],0))

Keep in mind that if [JobTicket] is a space or empty string, then Val() will convert to zero.

You can use this formula in a column that you use for sorting if you want to keep the text number column.  Just another way of solving the issue of sorting text column as a number, although I'll bet someone will find fault with it because it has one too many characters in it or some other made-up reason.