Date to appear

[Alex Motilal]

Dear Friends
I am generating a Processing Report, Date Wise of cotton ginned.

In the Dialog Box Form to generate the Report I have two controls namely First Date and Last Date.
When I enter the dates and click Preview button the Report generates for the dates between the First Date and Last Date.
The first column of the report is Date column and the subsequent columns are production details, and the details are returned by using the DSum and DLookup functions.


Certain dates in between the Dates entered may not have production details. However I want those dates also to appear in the report. I can use Nz function to return zero value.

So please help me so that the dates of non-production also should appear.
Thanks

[orange]

Investigate LEFT JOIN that will show all records from Left table and only those matching records in right table. More info about your tables and requirements may help readers to better understand your needs.
see w3schools sql

[Alex Motilal]

Thanks Orange.
However the tblProcessing which is the report based on will have only dates for which processing is done. I want the date to appear for which no processing done.
Regards,
Alex

[orange]

I think you are going to have to identify such dates and let Access know about them- in some manner.
You could have a table of Dates --all dates in the Time period.
Perhaps someone has a sample to show.

[orange]

Here is a short mock up: StartDate 1 Oct 2015.....EndDate 8 Oct 2015

Consider 2 tables: tblProduction with ProdDate and Amount

ProdDateId ProdDate ProdAmt
1 02/10/2015 100
2 03/10/2015 250
3 07/10/2015 300

and table tblMyDates a list of Dates in the time period

dateID DateToReport
1 01/10/2015
2 02/10/2015
3 03/10/2015
4 04/10/2015
5 05/10/2015
6 06/10/2015
7 07/10/2015
8 08/10/2015

Then a query with a Left Join

SELECT 
tblMyDates.DateToReport
, tblProduction.ProdDate
, tblProduction.ProdAmt
FROM tblMyDates LEFT JOIN tblProduction ON 
tblMyDates.DateToReport = tblProduction.ProdDate;

With results:

DateToReport ProdDate ProdAmt
01/10/2015
02/10/2015 02/10/2015 100
03/10/2015 03/10/2015 250
04/10/2015
05/10/2015
06/10/2015
07/10/2015 07/10/2015 300
08/10/2015