By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,474 Members | 3,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,474 IT Pros & Developers. It's quick & easy.

How to return all the dates from a period (from a date to another date)

P: n/a
JD
Thanks,

JD
Europe
Feb 14 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Feb 14, 4:53 am, "JD" <jdwacc...@hotmail.comwrote:
Thanks,

JD
Europe
where someDate between #01/01/2007# and #02/14/2007#

Feb 14 '07 #2

P: n/a
On Feb 14, 6:53 am, "JD" <jdwacc...@hotmail.comwrote:
Thanks,

JD
Europe

Please don't multi-post.

First, create an auxiliary table, say tblIntegers, that looks like:

tblIntegers
ID AutoNumber
theInt Long

ID theInt
1 1
2 2
3 3
4 4
....
20 20

such that the number of entries is at least equal to the number of
dates in the range (inclusive). The theInt values should contain no
gaps unless you want to exclude dates.

Then (Note: This example uses U.S. dates but should work correctly for
European dates):

qryAllDatesInRange:
SELECT DateAdd("d", theInt - 1, #2/2/07#) AS theDates FROM tblIntegers
WHERE DateAdd("d", theInt - 1, #2/2/07#) BETWEEN #2/2/07# AND #2/5/07#
ORDER BY theInt;

!qryAllDatesInRange:
theDates
2/2/2007
2/3/2007
2/4/2007
2/5/2007

As a parameter query:

qryAllDatesInRange:
PARAMETERS dtStart Date, dtEnd Date;
SELECT DateAdd("d", theInt - 1, dtStart) AS theDates FROM tblIntegers
WHERE DateAdd("d", theInt - 1, dtStart) BETWEEN dtStart AND dtEnd
ORDER BY theInt;

!qryAllDatesInRange:

Enter Parameter Value
dtStart
2/2/07

Enter Parameter Value
dtEnd
2/5/07

theDates
2/2/2007
2/3/2007
2/4/2007
2/5/2007

James A. Fortune
CD********@FortuneJames.com

Feb 14 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.