Solved

IIF and Nz

Posted on 2015-01-04
6
116 Views
Last Modified: 2015-01-04
Experts, I have a table with a field named [Completed].  This table is an import from Excel.  The field value is either Yes, No or "".    If [Completed] is null or "" then the return should be "No".  

What do you see as wrong in the below statement?  I seem to always get "Yes" for any value in  [Completed] whether it is Yes or "No" or ""

Completed_Group: IIf(Nz([Completed],""=""),"No","Yes")
0
Comment
Question by:pdvsa
6 Comments
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 40530412
Try this code:
IIf(Nz([Completed],"") ="","No",[Completed])
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40530413
Try
IIf(Nz([Completed], """"), "No", "Yes")

You were trying to set to "No" any thing that was "=". Not sure where you got = from?

Kelvin
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40530422
this should work:
Completed_Group: IIf(Nz([Completed]=""),"No",[Completed])
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40530426
Explanation: Nz([Completed] will give "" in case the field is "" or Null
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40530447
try

Completed_Group: IIf([Completed] & ""="","No", [Completed])

this will give you
No for Null
No for No
Yes for Yes
0
 

Author Closing Comment

by:pdvsa
ID: 40530477
that was it.  thank you.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

932 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

13 Experts available now in Live!

Get 1:1 Help Now