Date selection query

[Guest]

I need to know dates of a day between 2 dates. For example, if 9/3/2007,
9/16/2007 and Wednesday are given, the query returns 9/5/2007 and 9/12/2007.

Any ideas?

 

[Gary Walter]

I need to know dates of a day between 2 dates. For example, if 9/3/2007,
9/16/2007 and Wednesday are given, the query returns 9/5/2007 and
9/12/2007.

Any ideas?

Hi sgyan1,

I'd just create a "tblCalendar"

In the overall scheme of things,
it is just not that expensive...

28 years of individual dates would
only be ~10,000 records

The easiest method would start in Excel
w/ A1 value of earliest date you might need.
Then, drag the right corner icon of the cell
down the page to the "future" (64K+ dates
possible).

Then import into Access table giving date
field some unreserved name like "CalDate."

Add any number of fields to table depending
on what you want (say "WkDayName") being
sure again not to use reserved Access words.

Then, run an update query

UPDATE tblCalendar
SET WkDayName = Format([CalDate],"dddd");

or

UPDATE tblCalendar
SET WkDayName =WeekDayName([CalDate]);

So...

your query then becomes

SELECT
CalDate
FROM
tblCalendar
WHERE
[WkDayName] = "Wednesday"
AND
[CalDate]
BETWEEN
#9/3/2007#
AND
#9/16/2007#;

good luck,

gary

 

[Guest]

Hi Gary,

It's a great idea that solves the issue very well, making it much easier to
design and code compared to using a mathmatical way to populate the results.

Thanks a lot

Daniel Yang