I currently am using the below SQL as the record Source for a report Grouped by ProdDate and Line Is there a means or method that I can use to place a Zero in the report for the days that are notpart of the record Set ? For example reporting from Jan 1 thru Jan 31 I have data within the Record Set for ALL days with the exception of Jan 30th and Jan 31st so these two dates would have Zero displayed on the report.

SELECT tblPalletSub.ProdDate, tbluLines.Line, Sum(tblPalletSub.lbsOpened) AS SumOflbsOpened
FROM tbluLines INNER JOIN tblPalletSub ON tbluLines.pkLineID = tblPalletSub.fkLineID
GROUP BY tblPalletSub.ProdDate, tbluLines.Line;

Thank You

You could do something like Field1Eval:IIF([Field1] is null,0,[Field1]) and do that for each field that you need evalauted.

Thank You Dan however more than likely I did a very poor job of presenting the issue.

The record source has ALL Proddates populated with a Date for example

01/01/08
01/02/08
thru
01/29/09

January 08 has 31 Days and
01/30/08
01/31/08

Are not part of the record set of the Query so I would like to place a zero (0) within the report reflecting that

01/30/08
01/31/08

Have No Data following the 01/29/08 Record

I really do not think it is possible just wanted to post the ??? in the event it was.

Thank You

John,

Take a look at the database I'm attaching, I think this will do what you're looking for.

Dan,

Your attachment must not have attached properly as I am getting an error meggage when trying to download.

Thank You

Let's try this again

One more time

I'm not sure if that one went through. Basically what you can do is create a table that holds just dates. The easiest way to do this is just type in a few dates in Excel and auto-fill the rest and then import into Access.

You would then set a relationship with your table on the imported dates table. In your query, you would use your table and the dates table and set the relationship, so all the fields from the dates table show. You can then do your statements e.g. Field1Eval:IIF([Field1] is null,0,[Field1])

Dan,

Thank You still a No Go on the attachment ( wonder why Attaching ) for whatever reason but will try your suggestion.

Thanks Again,

I'm not sure why it won't post. I just uploaded a file in another post and that went through OK. I'm at work, so that might have something to do with it, the server goes through stages where it just times out.