Create List of Dates that fall between 2 Dates

[Diggsy]

Hello,
I am tyrying to create a query that will list All the dates between two
dates. For instance I have the Date January 1 in the Start Field and have
January 3 in the End Field.

Name Start Date End Date
Bob 01/01/09 01/03/09

I would like the result

Bob 01/01/09
Bob 01/02/09
Bob 01/03/09
Any suggestions would be greatly appreciated

thanks

Chris

 

[Duane Hookom]

I would create a table of numbers with a single field and then add values
from 0 to whatever number:

tblNums
=====
[Num]
0
1
2
3
4

Add this into a query with your table containing Start and End fields.
Create a column with this expression:
NewDate: Start + Num
Set the criteria under this column to
Between [Start] and [End]

This should return the appropriate dates.

 

[MGFoster]

Hello,
I am tyrying to create a query that will list All the dates between two
dates. For instance I have the Date January 1 in the Start Field and have
January 3 in the End Field.

Name Start Date End Date
Bob 01/01/09 01/03/09

I would like the result

Bob 01/01/09
Bob 01/02/09
Bob 01/03/09
Any suggestions would be greatly appreciated

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I got the following from a newsgroup. Unfortunately, I didn't save the
name of the poster.

============= begin quote =======================
If you have a table with sequential numbers in it,
Table: Iotas
Field: Iota (numbers from 1 to the max needed) - unique

SELECT YourTable.[Id Name]
, DateAdd("d",Iotas.Iota -1,StartDate) as theDate
FROM Iotas, YourTable
WHERE Iotas.Iota <= YourTable.Days

The number table is handy to have and you only need to create as many
records in it as the highest number you want to use. In this example
you would only need 9 records - if that were your maximum value in the
Days field.

You can actually use just one table with the numbers from 0 to 9 and
then use the table multiple times (aliased) to get any number you want

Iotas1000.Iota * 1000 + Iotas100.Iota * 100 + Iotas10.Iota*10 +
Iotas.Iota would generate every number from 0 to 9999

============= end quote =======================
HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBScwHjYechKqOuFEgEQJ7twCdE3THbplKnLVdZq8K+Tu8L78FgtQAniTh
CnjL04rsy9rI6kNDMhqR/pgX
=td/n
-----END PGP SIGNATURE-----

 

[Diggsy]

Thank you so much for your help

I would create a table of numbers with a single field and then add values
from 0 to whatever number:

tblNums
=====
[Num]
0
1
2
3
4

Add this into a query with your table containing Start and End fields.
Create a column with this expression:
NewDate: Start + Num
Set the criteria under this column to
Between [Start] and [End]

This should return the appropriate dates.

--
Duane Hookom
Microsoft Access MVP

Hello,
I am tyrying to create a query that will list All the dates between two
dates. For instance I have the Date January 1 in the Start Field and have
January 3 in the End Field.

Name Start Date End Date
Bob 01/01/09 01/03/09

I would like the result

Bob 01/01/09
Bob 01/02/09
Bob 01/03/09
Any suggestions would be greatly appreciated

thanks

Chris

 

[raskew via AccessMonster.com]

Hi -

This appears to be the source:
http://www.tech-archive.net/Archive/Access/microsoft.public.access.queries/2008-11/msg00030.html


Bob

Hello,
I am tyrying to create a query that will list All the dates between two

[quoted text clipped - 10 lines]

Bob 01/03/09
Any suggestions would be greatly appreciated

I got the following from a newsgroup. Unfortunately, I didn't save the
name of the poster.

============= begin quote =======================
If you have a table with sequential numbers in it,
Table: Iotas
Field: Iota (numbers from 1 to the max needed) - unique

SELECT YourTable.[Id Name]
, DateAdd("d",Iotas.Iota -1,StartDate) as theDate
FROM Iotas, YourTable
WHERE Iotas.Iota <= YourTable.Days

The number table is handy to have and you only need to create as many
records in it as the highest number you want to use. In this example
you would only need 9 records - if that were your maximum value in the
Days field.

You can actually use just one table with the numbers from 0 to 9 and
then use the table multiple times (aliased) to get any number you want

Iotas1000.Iota * 1000 + Iotas100.Iota * 100 + Iotas10.Iota*10 +
Iotas.Iota would generate every number from 0 to 9999

============= end quote =======================
HTH,