Jun 18 2008, 06:51 PM
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;
Jun 20 2008, 02:37 PM
You could do something like Field1Eval:IIF([Field1] is null,0,[Field1]) and do that for each field that you need evalauted.
Jun 20 2008, 03:13 PM
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
January 08 has 31 Days and
Are not part of the record set of the Query so I would like to place a zero (0) within the report reflecting that
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.
Jun 23 2008, 09:46 AM
Take a look at the database I'm attaching, I think this will do what you're looking for.
Jun 23 2008, 09:55 AM
Your attachment must not have attached properly as I am getting an error meggage when trying to download.
Jun 23 2008, 10:26 AM
Let's try this again
Jun 23 2008, 10:27 AM
One more time
Jun 23 2008, 10:31 AM
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])
Jun 23 2008, 10:35 AM
Thank You still a No Go on the attachment ( wonder why Attaching
) for whatever reason but will try your suggestion.
Jun 23 2008, 10:45 AM
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.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here