IIf statement

[KiowaPilot]

I have a query here that I built It works absolutely fine but I would like to
add one more tweak to it. It leaves out names of individuals on the pilots
table that do not have an entry that =NG. I believe that an IIF operator
would go in the slq but I am having trouble making it work. Not having their
names show up I think, is causing a report that I have built to be incomplete.

SELECT Pilots.LName, Max(Flight_Input.FLT_DATE) AS MaxOfFLT_DATE,
Flight_Input.FS_ID
FROM Flight_Input INNER JOIN Pilots ON Flight_Input.LNAME = Pilots.LName
GROUP BY Pilots.LName, Flight_Input.FS_ID
HAVING (((Flight_Input.FS_ID)="NG"));

I imagine that it would look something like:
SELECT Pilots.LName, IIF(Max(Flight_Input.FLT_DATE)=Null, "No NG",
[FLT_DATE]) AS MaxOfFLT_DATE, Flight_Input.FS_ID
FROM Flight_Input INNER JOIN Pilots ON Flight_Input.LNAME = Pilots.LName
GROUP BY Pilots.LName, Flight_Input.FS_ID
HAVING (((Flight_Input.FS_ID)="NG"));

 

[Tom Wickerath]

Hi KiowaPilot,

I imagine that it would look something like:
SELECT Pilots.LName, IIF(Max(Flight_Input.FLT_DATE)=Null, "No NG",
[FLT_DATE]) AS MaxOfFLT_DATE

You cannot test for equality to Null, since null is undefined. However, you
can use the built-in IsNull function. Perhaps something like this?

SELECT Pilots.LName,
IIF(IsNull(Max(Flight_Input.FLT_DATE), "No NG", [FLT_DATE] AS MaxOfFLT_DATE))


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

I have a query here that I built It works absolutely fine but I would like to
add one more tweak to it. It leaves out names of individuals on the pilots
table that do not have an entry that =NG. I believe that an IIF operator
would go in the slq but I am having trouble making it work. Not having their
names show up I think, is causing a report that I have built to be incomplete.

SELECT Pilots.LName, Max(Flight_Input.FLT_DATE) AS MaxOfFLT_DATE,
Flight_Input.FS_ID
FROM Flight_Input INNER JOIN Pilots ON Flight_Input.LNAME = Pilots.LName
GROUP BY Pilots.LName, Flight_Input.FS_ID
HAVING (((Flight_Input.FS_ID)="NG"));

I imagine that it would look something like:
SELECT Pilots.LName, IIF(Max(Flight_Input.FLT_DATE)=Null, "No NG",
[FLT_DATE]) AS MaxOfFLT_DATE, Flight_Input.FS_ID
FROM Flight_Input INNER JOIN Pilots ON Flight_Input.LNAME = Pilots.LName
GROUP BY Pilots.LName, Flight_Input.FS_ID
HAVING (((Flight_Input.FS_ID)="NG"));