Show all dates (1 Viewer)

[Infinite]

Hello! I have a tblShowSales, and it has sale dates, gross sales, and quantity. I want to get the totals of those by date, but I want ALL the dates. So, lets say I have 2 sales as follows:

Quantity-Gross Sales---Date
1---------$9.97--------- 11/2/2015
1---------$9.97--------- 11/4/2015


I want it to return:

Quantity-Gross Sales---Date
1---------$9.97--------- 11/2/2015
0---------$0.00--------- 11/3/2015
1---------$9.97--------- 11/4/2015

How would I do that? Thanks!

 

[sneuberg]

This looks like a outer join on a table with all the dates, but I suspect you don't have such a table. You could generate a table in VBA by looping from the lowest date to the highest date.

 

[Infinite]

How would I do that?

 

[sneuberg]

You could do it with code like:

Dim StartDate As Date
Dim EndDate As Date
Dim OutputDate As Date
Dim db As DAO.Database
Set db = CurrentDb

StartDate = DMin("[Sale Date]", "[tblShowSales]")
EndDate = DMax("[Sale Date]", "[tblShowSales]")
db.Execute "DELETE * FROM [DateTable];"
For OutputDate = StartDate To EndDate
    db.Execute "INSERT INTO [DateTable] (CalendarDate) VALUES (#" & OutputDate & "#);"
Next OutputDate
db.Close

DoCmd.OpenReport "Sales Report", acViewReport

which you can find in the attached database in the command button event. You'll will also find a query (Sales Query) which shows how to set up the outer join. Note that the null values left behind by the join need fixing up with Nz and Format in this query.

Also note that this solution uses a table (DateTable) to hold the range of dates. This gets emptied and repopulated each time the program is run which could lead to bloat. You may need to compact and repair more often. I actually hope some other forum member comes up with a better solution for you. I'd like to see it.


Let us know if you have any questions.