Help with building a query (manipulating dates) 1

[jeevenze]

I need help writing an SQL statement. I want to prepopulate a table with all the dates for the year and i want a query to select only the current date and 21 days that have passed. For example, today the query would select all the dates inbetween 06/27/2001 - 06/6/2001.
Thank you in advance to anyone who responds

Jeevenze

 

[nickjar2]

i am not giving very good answers today but maybe try:

between date()-21 and date()

in the criteria field for your 2nd query.

Nick

 

[nickjar2]

something on the lines

dim db as database
dim rs as recordset
dim dDate as date
dim i as integer

set db = currentdb
set rs = db.openrecordset("TableName",dbopendynaset)
dDate = #01/01/2001# ' not sure if u need the #'s

for i = 1 to 365

rs.addnew (or edit, depending on what u r doing)
rs.fields(0) = dDate + i
rs.update

next i

 

[mymou]

The easy way to populate an empty table would be to generate the data in Excel - save the file and import. Excel can generate these date automatically.

Sounds like an odd table.


Stew

 

[jeevenze]

It probably is 'odd'...but i'm trying to build an online time(sheet/management) and I want a pull down menu with dates and I just want the pulldown to display three weeks worth of dates. I did get it work like i wanted...I inserted the dates for two years in an access table and I used the following query

SELECT [tbldate].[zdate], [tbldate].[zday]
FROM tbldate
WHERE ((([tbldate].[zdate]) Between Date() And Date()-21));

and it works now.
(I did use your idea of generating the dates in excel (used the auto-fill).
Thanks

Jeevenze