By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,203 Members | 1,636 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,203 IT Pros & Developers. It's quick & easy.

Combining IsNull and IIf in an Expression

P: 18
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...

Expand|Select|Wrap|Line Numbers
  1. Revision_box: IIF(IsNull([Round_1]), "0", IsNull([Round_2]), "0", "2", IsNull([Round_3]), "0", "3")
Oct 28 '09 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. 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

Share this Question
Share on Google+
8 Replies


Expert 100+
P: 266
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. 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
Oct 28 '09 #2

P: 18
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
Oct 28 '09 #3

NeoPa
Expert Mod 15k+
P: 31,767
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.
Oct 29 '09 #4

P: 18
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CAR_Revision_1()
  2.  
  3. If [CAR EVMC Response Comment (1)] = IsNull Then [CAR_Revision_1] = "3"
  4. ElseIf [CAR CMO Response Comment (1)] = IsNull Then [CAR_Revision_1] = "2"
  5. ElseIf [CAR CMO Response Comment (2)] = IsNull Then [CAR_Revision_1] = "1"
  6.  
  7. Else: [CAR_Revision_1] = "0"
  8.  
  9. End If
  10.  
  11. End Sub 
Nov 2 '09 #5

P: 18
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.

Expand|Select|Wrap|Line Numbers
  1. 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")
Nov 2 '09 #6

Expert 100+
P: 266
@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:
Expand|Select|Wrap|Line Numbers
  1. 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
Nov 2 '09 #7

NeoPa
Expert Mod 15k+
P: 31,767
@DBlearner
It's not actually VBA code you need here. It's SQL. Try :
Expand|Select|Wrap|Line Numbers
  1. [CAR_Revision_1]: Switch([CAR EVMC Response Comment (1)] Is Null,'3',
  2.                          [CAR CMO Response Comment (1)] Is Null,'2',
  3.                          [CAR CMO Response Comment (2)] Is Null,'1',
  4.                          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.
Nov 2 '09 #8

P: 18
It worked perfectly. Thank you so much for the help. I'm learning and loving it. Thanks man!
Nov 17 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.