Add Date When No Data is Present

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that is created from a linked database that is derived from
another computer system and is updated hourly with information from the
current date and 90 days out. The report lists a schedule of events and most
days there is something that is happening. When there is nothing happening
on days I would like the report to populate with the date and standard
verbage, i.e. "No Events Scheduled". Is there anyway to do this.

Dave
 
DHICKS said:
I have a report that is created from a linked database that is derived from
another computer system and is updated hourly with information from the
current date and 90 days out. The report lists a schedule of events and most
days there is something that is happening. When there is nothing happening
on days I would like the report to populate with the date and standard
verbage, i.e. "No Events Scheduled". Is there anyway to do this.
Click to expand...


If your table does not contain records for some dates, then
you need to create a table that contain all possible dates.
You can then Left Join this dates table to your data table
to get blank data for the missing dates.

Creating such a table can be a pain and it can become
obsolete over time. To generalize this you can use a more
generic table (named Numbers) with one field (named Num) and
records that have value 1,2, . . ., NN where NN is larger
than whatever you need (in this can 90). Then you can
create a virtual table with the needed dates by using a
query named AllDates:
SELECT DateAdd("d", Num - 1, Date()) As DateField
FROM Numbers
WHERE Num < 90

Now you can join that to your data table in another query:
SELECT AllDates.DateField, datatable.*
FROM AllDates LEFT JOIN datatable
ON AllDates.DateField = datatable.eventdate
 
Back
Top