Date criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mary - 07 Dec 2005 21:11 GMT

Hello,

I need help creating a criteria that finds data for the previous week. Our
week starts on Mondays and ends Sundays. So if I ran it today (Wen dec 7) it
would find data from Mon Nov 28 through Sun Dec 4th.

Thanks,

Mary

Reply to this Message

fredg - 07 Dec 2005 21:30 GMT

> Hello,
>
[quoted text clipped - 5 lines]
>
> Mary

Look up the DatePart function in VBA help.
If the first day of the week is Monday, vbMonday has a value of 2, so:

WHERE DatePart("ww",[DateField],2)=DatePart("ww",Date(),2)-1

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Mary - 07 Dec 2005 22:25 GMT

Hi there,

It works! Thanks!

Now, I'm trying to do the same for the previous quarter. I changed the "ww"
to "q" per the help file. The problem is it grabs the data for the same
"previous quarter" but for last year... So I get September data for 05 and
04. It's doing the same when I change it to previous month "m"... I must be
missing something in the expression.

Maybe I need to use DateSerial for that type of expression? I know how to do
that for previous month and previous year, but not previous quarter.

Thanks,

Mary

> > Hello,
> >
[quoted text clipped - 14 lines]
> Please respond only to this newsgroup.
> I do not reply to personal e-mail

Reply to this Message

John Spencer - 08 Dec 2005 19:40 GMT

Use the dateAdd function to adjust the date

WHERE DatePart("q",[DateField],2)=DatePart("q",DateAdd("m",-3,Date()),2)

If you have multiple years worth of data then you will get data for the
quarter for every year. You can try the following criteria to limit it to
the prior quarter.

Where [DateField] Between
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)-2,1) AND
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,0)

That should translate to
Where [DateField] Between #7/1/2005# and #9/30/2005#

> Hi there,
>
[quoted text clipped - 37 lines]
>> Please respond only to this newsgroup.
>> I do not reply to personal e-mail

Reply to this Message