Solved

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

Posted on 2013-06-05
4
413 Views
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;
0
Comment
Question by:gigifarrow
[X]
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
4 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 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")
0
 
LVL 12

Assisted Solution

by:pdebaets
pdebaets earned 250 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.
0
 

Author Comment

by:gigifarrow
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)
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses
Course of the Month11 days, 1 hour left to enroll

632 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