Enumerate the Days (all the days) Between Two Dates (?)

  • Thread starter Thread starter croy
  • Start date Start date
C

croy

I have a table of surveys. The surveys are not done every
day. There is several years of data.

I need to show date for *every* day (whether a survey was
done or not), and show a total of survey details for each
day (even if it is zero).

Can I use a query to create a recordset that DateDiffs the
first & last survey dates, giving me a total of days between
the two dates, and then enumerate those days, as if it were
a table?
 
croy said:
I have a table of surveys. The surveys are not done every
day. There is several years of data.

I need to show date for *every* day (whether a survey was
done or not), and show a total of survey details for each
day (even if it is zero).

Can I use a query to create a recordset that DateDiffs the
first & last survey dates, giving me a total of days between
the two dates, and then enumerate those days, as if it were
a table?
Click to expand...


You will need a table somewhere in the process. I always
include a utility table (named Numbers) with one (indexed)
long integer field (named Num) and populated with values
0,1,2,3, ... up to a few thousand. Use this table whenever
you need a list of made up values.

It's easy to get your min and max dates by using simple
subqueries:

SELECT DateAdd("d", Numbers.Num, (SELECT Min(X.surveydate)
FROM table As X)) As AllDates,
FROM Numbers
WHERE DateAdd("d", Numbers.Num, (SELECT Min(X.surveydate)
FROM table As X)) <= (SELECT Max(Y.surveydate)
FROM table As Y)

and then fit your survey details into that.
 
Back
Top