Access Null Confusion

Simple query using query design view.  I have with account name, account, ID

If I run the query asking if account is null it lists all of the results where there is no account number but where there is an ID

I want to say that if the account is null then make the value default to that of the ID.

I have done this thousands of times but for some reason it isnt working here.  

I have

IIF([Account] is null, [ID], [Account])

Simple but it returns no values

Thanks in advance for your help.
Who is Participating?
NUdovich2Connect With a Mentor Author Commented:
Thank you both, yep I am sure I am dealing with nulls (numeric) not empty strings though I tried both.  What solved it and what made it work was removing the ID field and making the field the expression so...

EXPR1:  IIf([Parent BPID] Is Null,[bpid],[parent BPID])

Therefore, viola.
mbizupConnect With a Mentor Commented:
Try this

mbizupConnect With a Mentor Commented:
That is just an alternative syntax,  but your syntax looks good too.  Are you sure you are dealing with nulls and not empty strings?
I just wanted to say that using the NZ function as mbizup suggested is more efficient than using the IIF function you used.  Your expression would look like this:

EXPR1:  Nz([Parent BPID], [bpid])

And it means exactly the same thing as your IIF expression.
NUdovich2Author Commented:
It is what ultimately worked.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.