Create all dates for a year in a query

  • Thread starter Thread starter James Frater
  • Start date Start date
J

James Frater

Hello Everyone,

Random question for everyone.

Is there anyway as part of a query I could generate all the dates in a year?
So for 2010 I'd like every date to be listed on a seperate row.

Many thanks

James
 
Create a table named CountNumber with number-long integer field named
CountNUM containing 0 (zero) through 365.
Use this calculated field in a query --
Calendar_2010: DateAdd("d", [CountNumber].[CountNUM], #1/1/2010#)
 
James

If you have to do this from within Access, Karl offers an approach.

If that isn't a constraint, how 'bout using Excel, entering the first few
dates, then "extending" that until you have your year-full-o-dates?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Hello Everyone,

Random question for everyone.

Is there anyway as part of a query I could generate all the dates in a year?
So for 2010 I'd like every date to be listed on a seperate row.

Many thanks

James
Click to expand...

Another way is to create an AllDates table with just one date/time field as
its primary key. Open Excel, and use Fill... Series to create a column with
dates covering the range you want (a decade is only 3653 rows or so, a trivial
size).

Join this table to your query with a left join to show all records in AllDates
and matching records in your query.
 
Back
Top