quarterly date queries generating annually

  • Thread starter Thread starter circuit slave
  • Start date Start date
C

circuit slave

Hi, can someone give me advice on best to have quarterly date queries
set automatically each year? Can this be done?

For example I have a report which has dates for four quaraters:

IIF([Date] Between #12/1/2004# And #2/28/2005# for example, this is The
first quarter.

But what I want to know is if there is a way to automatically update
the year of each quarter, when the query is run.

I hope that makes sense, Thanks in advance.
 
Use DateSerial() to construct a date, where the year part (first argument)
comes from Year(Date()).

Use DateAdd("m", -1, ...) to subtract 1 month to get the quarters you use.

You can also use month zero and day zero to step back one month or one day.
For example, your first quarter would be:
Between DateSerial(Year(Date()),0,1) And DateSerial (Year(Date()),3,0)
 
Hi, can someone give me advice on best to have quarterly date queries
set automatically each year? Can this be done?

For example I have a report which has dates for four quaraters:

IIF([Date] Between #12/1/2004# And #2/28/2005# for example, this is The
first quarter.

But what I want to know is if there is a way to automatically update
the year of each quarter, when the query is run.

I hope that makes sense, Thanks in advance.
Click to expand...

Two ways:

DatePart("q", [Date])

will return 1, 2, 3 or 4 for the quarters, and can be used as a search
criterion.

Better (because it's faster and can use table indexes) would be to
have a criterion
= DateSerial([Enter year:], 3*[Enter quarter:] - 2, 1) AND < DateSerial([Enter year:], 3*[Enter quarter:] + 1, 1)
Click to expand...

on the date field, to prompt for the desired year and quarter.

John W. Vinson[MVP]
 
Thanks for everybody's help;however, this is just for a report, so it
won't have user entry to select year. I just want the report to
automatically generate the year, since the quarters remain the same.

It's used for attendance at work. So how can I incorporate it? Here
is the info:

1st Quarter: IIf([Date] Between #12/1/2004# And #2/28/2005#,[Points],0)
2nd Quarter: IIf([Date] Between #3/1/2005# And #5/31/2005#,[Points],0)
3rd Quarter: IIf([Date] Between #6/1/2005# And #8/31/2005#,[Points],0)
4th Quarter: IIf([Date] Between #9/1/2005# And #11/30/2005#,[Points],0)



John said:
Hi, can someone give me advice on best to have quarterly date queries
set automatically each year? Can this be done?

For example I have a report which has dates for four quaraters:

IIF([Date] Between #12/1/2004# And #2/28/2005# for example, this is The
first quarter.

But what I want to know is if there is a way to automatically update
the year of each quarter, when the query is run.

I hope that makes sense, Thanks in advance.
Click to expand...

Two ways:

DatePart("q", [Date])

will return 1, 2, 3 or 4 for the quarters, and can be used as a search
criterion.

Better (because it's faster and can use table indexes) would be to
have a criterion
= DateSerial([Enter year:], 3*[Enter quarter:] - 2, 1) AND < DateSerial([Enter year:], 3*[Enter quarter:] + 1, 1)
Click to expand...

on the date field, to prompt for the desired year and quarter.

John W. Vinson[MVP]
Click to expand...
 
Take a look again at the example I gave you:
Between DateSerial(Year(Date()),0,1) And DateSerial (Year(Date()),3,0)
That requires no user input.

So:
Q1: IIf([DateField] >= DateSerial(Year(Date()),0,1)
AND [DateField] <= DateSerial (Year(Date()),3,0), [Points], 0)

You can figure out the other quarters from there.
 
Back
Top