Help w/NZ 1

[kentwoodjean]

Below is the SQL of my query where I am trying to have my query populate a 0 if there are no records for Receipts/Closes. Currently it will do that but will not give the date. I use "Date1" as my primary date for receipts and closes, thereby aligning the Date1 receipts w/the close date for closes. If there are 0 receipts, I will get my numbers but no corresponding date which should be yesterday. Can anyone help please?

SELECT CountRecFacOnly.Date1, Sum(Nz([CountOfDate1],0)) AS CountDate1, Sum(Nz([CountOfClosed Dt],0)) AS [CountClosed Dt]
FROM CountRecFacOnly RIGHT JOIN CountClosFacOnly ON CountRecFacOnly.Date1 = CountClosFacOnly.[Closed Dt]
GROUP BY CountRecFacOnly.Date1;

 

[PHV]

SELECT Nz(CountRecFacOnly.Date1, Date()-1) AS ReceiptDate, ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kentwoodjean]

Works good. Thanks for the tip.