Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


How to make a iiF statement work based on a criteria being not null.

Posted on 2013-06-05
Medium Priority
Last Modified: 2013-06-21
Okay this query does things based on production number and the fields are check mark.

I need to add a criteria to all of this. which do not calculate fields unless in shop date is not null.  The code below works fine.

I tried WHERE ((Not (tblTempleFacilityFullup.InShopDate) Is Null)

SELECT F.Production, F.InShopDate, F.SerialNumber, F.Unit, F.Location, F.Induction, F.TearDown, F.Provision, F.E1GroundInsert, F.ReliefHoleExtinguisher, F.AssemblyA, F.FuelShotOffSleeve, F.AsemblyB, F.CEPUpgrade, F.AFESCEPSwitch, F.AssemblyC, F.ERRHandle, F.DriverSeat, F.AssemblyD, F.HotBoxEnhanceMent, F.AssemblyE, F.LegacyBattery, F.[LED/ERR], F.T161Track, F.AFES, F.HotBox, F.BASSM2, F.GunnersSeatStopRework, F.RoadTest, F.QC, F.OutDuction, F.DateCompleted, F.DateReturned, F.Remarks, F.FeriteBead, F.HotBox57K6608, F.AOA, F.BRATP, F.RollerHousingMOD, IIf([Production]=343-346 Or [Production]=347-374 Or [Production]=378 Or [Production]=379-380 Or [Production]=391-403 Or [Production]=405-407 Or [Production]=409-410 Or [Production]=412-422 Or [Production]=424-428 Or [Production]=432-464 Or [Production]=467 Or [Production]=469 Or [Production]=471-507 Or [Production]=524-536,Format(Abs(([F]![Induction]*6+[F]![TearDown]*10+[F]![Provision]*10+[F]![AssemblyA]*10+[F]![AsemblyB]*10+[F]![AssemblyC]*10+[F]![AssemblyD]*10+[F]![AssemblyE]*10+[F]![RoadTest]*4+[F]![QC]*10+[F]![OutDuction]*10)/100),"00.0%"),Format(Abs(([F]![Induction]*5+[F]![TearDown]*10+[F]![Provision]*0+[F]![AssemblyA]*20+[F]![AsemblyB]*0+[F]![AssemblyC]*0+[F]![AssemblyD]*20+[F]![AssemblyE]*25+[F]![RoadTest]*0+[F]![QC]*10+[F]![OutDuction]*10)/100),"00.0%")) AS Percentages
FROM tblTempleFacilityFullup AS F;
Question by:gigifarrow
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 93

Accepted Solution

Patrick Matthews earned 1000 total points
ID: 39223785
To test for (not null) in the WHERE clause:

WHERE SomeColumn Is Not Null

To test in an IIf():

IIF(SomeColumn Is Not Null, "Not Null", "Null")
LVL 12

Assisted Solution

pdebaets earned 1000 total points
ID: 39223923
You've got a big IIF statement in there that starts out like this:

IIf([Production]=343-346 Or [Production]=347-374 ...

I think that will be a problem because you don't have quotes around the "343-346". Access may treat that as a subtraction so [Production]= 343-346 might be equivalent to [Production] = -3. I don't think you want that. If [Production] is a text field, then try [Production] = "343-346". Or even better, try

IIf([Production] IN ("343-346", "347-374", "378", "379-380", ...), <true part>, <false part>)

... where "..." means put the rest of your list of production numbers in there.

Author Comment

ID: 39225276
That is not correct this is not the same question. It is the same code but I am trying to add another criteria to it . Please read the other question throughly. This question should not be deleted.

The code works fine. Im trying to add :

WHERE ((Not (tblTempleFacilityFullup.InShopDate) Is Null)

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

664 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