Combining IsNull and IIf in an Expression

[DBlearner]

Good afternoon folks, I'm a total novice at this game.

I'm a student at an University in DC and is taking a basic course in Access and database. I need some help writing a nesting expression using Isnull and iff functions.

I have a field titled "Revision_box" that would display either 0,1,2, or 3 depending if certain fields are empty or has been inputted in.

So,

If Round 1 field is empty then Rev box would be "0" but if inputted then 1.
If Round 2 field is inputted then Rev box would be "2".
If Round 3 field is inputted then Rev box would be "3".

Does that make sense? I wrote a code for it but it doenst work...

Revision_box: IIF(IsNull([Round_1]), "0", IsNull([Round_2]), "0", "2", IsNull([Round_3]), "0", "3")

[ajalwaysus]

I don't believe we are allowed to give you an answer to a class question, but I believe I can give you some advice, I see what is wrong.

You need to learn more about the IIF functon. You are assuming that you can you can make multiple evaluations in a single IIF statement, that is not correct. Each IIF statement expects 1 condition and 1 if false and 1 if true statement, like:

iif (condition, value_if_true, value_if_false )

If you want to do more, you need to embed more IIF inside each other like below:

iif (condition, value_if_true, iif (condition, value_if_true, iif (condition, value_if_true, iif (condition, value_if_true, value_if_false ))))

Hope this points you in the right direction.

-AJ

[DBlearner]

Thank you for the quick response.

This is not for grade but my personal attempt to understand expressions and how to properly nest functions. Anyway, I understand your code and it makes sense. But there's a problem... I need to use Isnull function at the beginning but cant find another function that is the opposite of Isnull for the subsequent nesting in the expression.

I would use IsNull at the beginning to tell the revision box to put in "0" when the field is null. However, how can I make the expression react accordingly when subsequent fields has been inputted?

God I love this!

Mike

[NeoPa]

You don't need to use the IsNull() function in a query. X Is Null works anywhere SQL code is used (in queries specifically). Either should work for you.

Checking for Null is a little different from ordinary checks as the only way to check for Nulls in VBA is to use the IsNull() function. In SQL X Is Null also works of course. Checking for something not being Null is the same except you use Not with it (X Is Not Null).

IIf() is pretty powerful and can be used extensively. However, other useful functions include Switch() & Choose(). You will find out more about these in the Help system. If you're learning Access you want to make a friend of the Help system. It exists for Access itself, as well as VBA from the IDE (VBA Editor) window. You'd be better served by Switch() in this instance.

[DBlearner]

Neo,

I didnt know about Switch, etc and I just learned several new functions, thx. I dont see how Switch() would be a good solution...

I ended up finding out about If-Then-Else statement and it's perfect for what I wanted to do.

I wrote this VBA code since this one can only work in VBA and not in Query. However, nothing happened and I am scratching my head.

Private Sub CAR_Revision_1()
 
If [CAR EVMC Response Comment (1)] = IsNull Then [CAR_Revision_1] = "3"
ElseIf [CAR CMO Response Comment (1)] = IsNull Then [CAR_Revision_1] = "2"
ElseIf [CAR CMO Response Comment (2)] = IsNull Then [CAR_Revision_1] = "1"
 
Else: [CAR_Revision_1] = "0"
 
End If
 
End Sub 

[DBlearner]

I decided to do a different tack and used a different funtion for a query table. I think this may be more efficient and easier to do than doing "If Then Else" statement in VBA. However, this one seem not to be working properly because of syntax issue.

CAR_Revision_1: IIf(Not IsNull([CAR EVMC Response Comment (1)]),"1", or (Not IsNull([CAR CMO Response Comment (1)]), "2", or (Not IsNull([CAR CMO Response Comment (2)]), "3", "0")

[ajalwaysus]

@DBlearner
Your issue is still the same as I pointed out before, you are assuming you can just use one IIF function, and just divide it with "OR"s. You cannot do that, for every possible "OR" you need to make another IIF.

As below:

CAR_Revision_1: IIf(Not IsNull([CAR EVMC Response Comment (1)]),"1", IIF(Not IsNull([CAR CMO Response Comment (1)]), "2", IIF(Not IsNull([CAR CMO Response Comment (2)]), "3", "0")))

I have not tested this, but this is the general idea.

-AJ

[NeoPa]

@DBlearner
It's not actually VBA code you need here. It's SQL. Try :

[CAR_Revision_1]: Switch([CAR EVMC Response Comment (1)] Is Null,'3',
                         [CAR CMO Response Comment (1)] Is Null,'2',
                         [CAR CMO Response Comment (2)] Is Null,'1',
                         True,'0')

The spacing is only necessary for display purposes here. In the query you can have it as a single line if you want.

[DBlearner]

It worked perfectly. Thank you so much for the help. I'm learning and loving it. Thanks man!