[Webinar] Streamline your web hosting managementRegister Today

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

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

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;
2 Solutions
Patrick MatthewsCommented:
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")
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.
gigifarrowAuthor Commented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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