• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

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.
0
NUdovich2
Asked:
NUdovich2
  • 2
  • 2
3 Solutions
 
mbizupCommented:
Try this

Nz(account,id)
0
 
mbizupCommented:
That is just an alternative syntax,  but your syntax looks good too.  Are you sure you are dealing with nulls and not empty strings?
0
 
NUdovich2Author 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.
0
 
IrogSintaCommented:
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.
0
 
NUdovich2Author Commented:
It is what ultimately worked.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now