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:
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!
I got this far with this:
IIF(IsNull([JobTicket]), "0" , "1")
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!
Microsoft AccessVBA
Last Comment
Mark Edwards
you don't need IsNull for this, use NZ([JobTicket],0)
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.
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
SortBy: IIF(NZ([JobTicket], 0) = 0 , 3000, [JobTicket])
Sort: Ascending
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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.
"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.
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.