Date criteria

  • Thread starter Thread starter Mary
  • Start date Start date
M

Mary

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
 
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
Click to expand...

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
 
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


fredg said:
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
Click to expand...

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
Click to expand...
 
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#


Mary said:
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


fredg said:
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
Click to expand...

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
Click to expand...
Click to expand...
 
Back
Top