Showing Null values in a query

[MarkNie]

Hi All

Hope you are well!!

I am having a problem showing null values in the following query. I want to show the records which where signed off late and those that haven't been signed off at all up to todays date. Any help would be greatly appreciated..

I have tried inserting OR ((qrybAttn.Date) Is Null)) but then it shows all the records and not just up to today.

SQL Code

INSERT INTO ytblAttnIPSLAmissed ( PortfolioCode, RunDeadline, [Attn Date], [IP Date], [SLA Date] )
SELECT tblTracking.PortfolioCode, tblTracking.RunDeadline, qrybAttn.Date, qrybIP.Date, qrybSLA.Date
FROM ((tblTracking LEFT JOIN qrybAttn ON tblTracking.TrackingID = qrybAttn.TrackingID) LEFT JOIN qrybIP ON tblTracking.TrackingID = qrybIP.TrackingID) LEFT JOIN qrybSLA ON tblTracking.TrackingID = qrybSLA.TrackingID
WHERE (((tblTracking.RunDeadline)<Now()) AND ((Format([qrybAttn].[Date],"Short Date"))>Format([RunDeadline],"Short Date")))
WITH OWNERACCESS OPTION;

Thanks again.
Regards
Mark

 

[traingamer]

There are several ways to do this. One is:

...((Format([b]nz([qrybAttn].[Date], Date(),[qrybAttn].[Date])[/b] ,"Short Date"))>Format...

although you can probably compare them without formatting to save some processing.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Of course, i just read your other post, so keep the format.


Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[MarkNie]

Hi traingamer

When I enter your could in it give me an error as follows Wrong number of arguments used with function in query expression.

Here is the code any ideas??

INSERT INTO ytblAttnIPSLAmissed ( PortfolioCode, RunDeadline, [Attn Date], [IP Date], [SLA Date] )
SELECT tblTracking.PortfolioCode, tblTracking.RunDeadline, qrybAttn.Date, qrybIP.Date, qrybSLA.Date
FROM ((tblTracking LEFT JOIN qrybAttn ON tblTracking.TrackingID = qrybAttn.TrackingID) LEFT JOIN qrybIP ON tblTracking.TrackingID = qrybIP.TrackingID) LEFT JOIN qrybSLA ON tblTracking.TrackingID = qrybSLA.TrackingID
WHERE (((tblTracking.RunDeadline)<Now()) AND ((Format(nz([qrybAttn].[Date], Date(),[qrybAttn].[Date]) ,"Short Date"))>Format([RunDeadline],"Short Date")))
ORDER BY tblTracking.PortfolioCode
WITH OWNERACCESS OPTION;

Thanks
Regards
Mark

 

[Golom]

NZ takes only two arguments and you have

Format(nz([qrybAttn].[Date], Date(),[qrybAttn].[Date]) 
                 1             2          3

 

[traingamer]

My mistake - I meant

...((Format(nz([qrybAttn].[Date], Date()) ,"Short Date"))>Format...

Confusing parameters of iif and nz.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill