How to fill gaps in between two dates while creating reports?

[rsdharan]

I request the experts to solve my problem in creating a report. In a Date field, there are gaps in between two dates. But, I want the report to be sequentially dated even if the date field in the table contains no value or remains null and other fields are null as well. When I create the report, the date field should be sequentially filled and against the date other field should contain text, such as "No Data found" or something like that.
Can anybody help?
rsdharan

 

[vbaInet]

Sequentially filled in what way? Can you show us some examples.

 

[rsdharan]

Sequentially filled in what way? Can you show us some examples.

On generating the report, the date field should be filled consecutively, even if there is a gap between two dates in the date field of a table, which is linked to a form. The person who feeds the data, does not fill the date when there is nothing to feed in on a particular date. Only those dates which are available in the field is shown while generting the report. What I need while generating a report is to get all the dates shown in between two dates criteria or for entiremonth.
Thanks for your concern.
rsdharan

 

[vbaInet]

Ok, so in a spreadsheet show us some sample data and highlight the values where it was sequentially filled in.

Consider these 3 rows

1/1/2012
--empty--
27/1/2012

What will the value of --empty-- be?

 

[rsdharan]

Ok, so in a spreadsheet show us some sample data and highlight the values where it was sequentially filled in.

Consider these 3 rows

1/1/2012
--empty--
27/1/2012

What will the value of --empty-- be?

These empty rows should contain the dates "consecutively dated"
such as
2/1/2012
3/1/2012
4/1/2012
5/1/2012
-----
-----
------
26/1/2012
in the report. Just as, for example, running sum is added in the Sl./No. field.
Thank U

 

[rsdharan]

These empty rows should contain the dates "consecutively dated"
such as
2/1/2012
3/1/2012
4/1/2012
5/1/2012
-----
-----
------
26/1/2012
in the report. Just as, for example, running sum is added in the Sl./No. field.
Thank U

What I mean to say is as follows:
I have linked some fields of a table to a form. While the user feeds the form, the data is stored to the table. By clicking a command button, a report is to be generarated. One of the fields contains date wherein only when there is a transaction the date is input , thus making the date field containing different dates of transaction only. But, while generating a report, the date column should be consecutively filled with all dates of a month. This is the result that I want to display in the report whereas the report as of now displays only those dates which are filled in the field. Can i use macro by adopting sevalue action.
Thank u
R.Sreedharan

 

[vbaInet]

It still doesn't answer my question. I asked a question about --empty-- and didn't get an appropriate answer. What will be filled in --empt--? Or you can use your example and tell me EXACTLY what dates will going into the blanks.

 

[rsdharan]

It still doesn't answer my question. I asked a question about --empty-- and didn't get an appropriate answer. What will be filled in --empt--? Or you can use your example and tell me EXACTLY what dates will going into the blanks.

In the example attached,View attachment Sample.xls the dates between 3/1/2012 and 8/1/2012 are not filled as there was no work carried out and similarly between 8/1/2012 and 12/1/2012 and between 13/1/2012 and 25/1/2012 etc. As may be seen for yourself. The data operator would fill in the form only when there is any work carried out. At the end of every month a report has to be generated containing all the dates of the month concerned consecutively, i..e. from 1/1/2012 to 31/1/2012 and the columns 2 to 4 will contain no data and column 5 should display “No Work Perfomed”. Hope I have explained elaborately.
Thanks

 

[vbaInet]

You can't just make up dates out of the blue. They need to exist in the table.

There might be a way using just queries but I will need to give it some thought.

However, the quick and easy way is to:

1. Create a query to return a DISTINCT list of dates
2. Using a recordset based on the query in 1, loop through and insert the missing dates into a new table.
3. Use a UNION to join your original query to the query in 2.
4. Use the Nz() function for the "No work performed" part.

The next time you run this process, you will need to amend step 1. So for step 1 get the DISTINCT dates from original query and join that to your temp table, then carry on with step 2 till step 4.

 

[boblarson]

You can use a separate table with dates listed and then join it to your other table using an outer join and use that separate table's date field as the date field in the report and the rest of the data in there.

Then in the query you would put something like:

NewFieldNameHere:IIf([OriginalFieldNameHere] Is Null, "No Data", [OriginalFieldNameHere]

You would need to do that for each one and then you would make sure the report's controls are bound to the new field name for each.

You can populate a table with all dates out to 2035 by using this code:

Function PopulateDateTable()
    Dim dteHold As Date
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb
    Set rst = CurrentDb.OpenRecordset("tblDates")
    dteHold = Date
    Do Until dteHold > #12/31/2035#
        With rst
            .AddNew
            !dDate = dteHold
            .Update
        End With
        
        dteHold = DateAdd("d", 1, dteHold)
    Loop
    MsgBox "Done"
    rst.Close
    Set rst = Nothing
    
End Function

 

[vbaInet]

You can populate a table with all dates out to 2035 by using this code

I wonder what you will be doing at that time.

 

[rsdharan]

I wonder what you will be doing at that time.

Ha! ha! ha! I will still be alive and guiding my grandchildren to contact this forum, where people like you will be of immense help.
As regards your solution, I tried to create the table containing dates till 2035 and suceeded. I am still working on getting the desired result and will let you know on completion.
Thank u
rsdharan

 

[vbaInet]

Ha! ha! ha! I will still be alive and guiding my grandchildren to contact this forum, where people like you will be of immense help.

We would have handed over to our children/grandchildren

As regards your solution, I tried to create the table containing dates till 2035 and suceeded. I am still working on getting the desired result and will let you know on completion.
Thank u
rsdharan

That was from Bob. Hopefully he'll be around to guide your grandchildren on what to do next in December of 2035

 

[boblarson]

The 2035 was intended to set it far out enough that you, or anyone else, would never need to set the dates again. It only takes about a second for it to populate the ~8,000 dates so no issue there. In fact, by that time, this database is likely not going to be around, but by then you certainly won't care.

 

[rsdharan]

We would have handed over to our children/grandchildren

That was from Bob. Hopefully he'll be around to guide your grandchildren on what to do next in December of 2035

Oh! I'm sorry! I failed to notice as vbaInet was replying to me. Thanks, mr Bob.