quarterly date queries generating annually

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

 

[Allen Browne]

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)

 

[John Vinson]

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.

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)

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

John W. Vinson[MVP]

 

[circuit slave]

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)

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.

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)

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

John W. Vinson[MVP]

 

[Allen Browne]

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.

 

[circuit slave]

Okay, thanks so much Allen. I appreciate it!