Solved

Access Null Confusion

Posted on 2012-03-16
5
285 Views
Last Modified: 2012-03-21
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
Comment
Question by:NUdovich2
  • 2
  • 2
5 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 500 total points
Comment Utility
Try this

Nz(account,id)
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 500 total points
Comment Utility
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
 

Accepted Solution

by:
NUdovich2 earned 0 total points
Comment Utility
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
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
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
 

Author Closing Comment

by:NUdovich2
Comment Utility
It is what ultimately worked.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now