Date Query (last week, last 2 weeks, last month, last year, all)

[danysfx]

Hi

Would be great if I could get some help on this:

I have big table that gets updated almost every day with new data. There is a date column. I have a Form where I can enter queries. I need to add a panel where I can spcify if the query should involve the data should involve the date from last week only, from the last 2 weeks, from the last month or if the query should involve the overall data.

It somhow need to be check what the date is today and then caclulate back.

Any help on this would be much appreciated.

Thanks
Daniel

 

[rainman89]

for this you could use the dateadd function
last week would be
dateadd("d",-7,date())
2 weeks
dateadd("d",-14,date()) etc..
month
dateadd("m",-1,date())

 

[danysfx]

Hi, thanks for that input

I am still not sure how to implement that into my form. What kind of list or box should I use, so that I can click on "last week" or "last month".

Thanks for further help
Daniel

 

[KeithG]

Why not just use a date range?

 

[danysfx]

for this you could use the dateadd function
last week would be
dateadd("d",-7,date())
2 weeks
dateadd("d",-14,date()) etc..
month
dateadd("m",-1,date())

It seems that

dateadd("d",-7,date())

is not giving the whole last week, it is giving the day that is 7 days back, e.g. today it is giving the date from 7 days ago, from the 24th only.

I need the whole last week, not only 1 day.

How do I have to use Daterange then?

Thanks
Daniel

 

[rainman89]

oh i misread what u were asking. i thought u were asking for the date of today for last week. ie today is thursday u wanted thursdays date. my next point was to tell u to use the BETWEEN function, but keiths DateRange will probably do you better. try searching the forum on it!

 

[danysfx]

Hi

Any more help on the BETWEEN or the daterange function would be much appreciated. I found this:

http://en.allexperts.com/q/Using-MS-Access-1440/Date-range-query.htm

But it does not really help.

Thanks
Daniel