Parameter Query - Date IIF

T

Tom Bock

I use a parameter query to enter the YEAR of a record.

I now would like to automatically populate the particular QUARTER for the
chosen year.


This is what I currently have:

**********
Year: (IIf([DATE] Between #1/1/2001# And #12/31/2001#,"2001",IIf([DATE]
Between #1/1/2002# And #12/31/2002#,"2002")))

This line above procudes the year so that I can easily group records.

**********



Here's now the problem with the "QUARTER":

**********
Quarter: (IIf([DATE] Between #1/1# & [YEAR] And #3/31# & [YEAR],"1st"))

Based on the entered parameter -- e.g. "2001" of the date field, the year
"2001" is pulled from the date field.
I now want to automatically compute the QUARTER based on the computed YEAR
field. In the example above (QUARTER), I have done it only for the 1st
quarter. Currently -- without the "&[YEAR]", the dates default
automatically to the current year e.g. #1/1/2004#.

Again, however, that's now what I want to produce for records that are
generated in 2001. I hope this makes sense?!?!

**********



Thanks,
Tom
 
B

Brian Camire

To find the year of a given date, use the Year function in an expression
like this:

Year([DATE])

To find the quarter of a given date, you might use the DatePart function in
an expression like this:

DatePart("q", [DATE])

In your query you want to select records from a specified year and quarter,
so enter each of the following expressions in the Field row of an empty
column in the query grid, and set the values in the criteria row in each
column to:

[YEAR]

and

[QUARTER]

respectively.
 
N

Nikos Yannacopoulos

Tom,

The Year one is even simpler (and quite faster for big tables):
Year: Year([Date])

For the Quarter:
Quarter:
Choose(Month([Date]),"1st","1st","1st","2nd","2nd","2nd","3rd","3rd","3rd","
4th","4th","4th")

HTH,
Nikos
 
T

Tom Bock

Nikos:

Thanks so much for the solution!!! This simplified YEAR version and the
Quarter function works really great!

Thanks again,
Tom



Nikos Yannacopoulos said:
Tom,

The Year one is even simpler (and quite faster for big tables):
Year: Year([Date])

For the Quarter:
Quarter:
Choose(Month([Date]),"1st","1st","1st","2nd","2nd","2nd","3rd","3rd","3rd","
4th","4th","4th")

HTH,
Nikos

Tom Bock said:
I use a parameter query to enter the YEAR of a record.

I now would like to automatically populate the particular QUARTER for the
chosen year.


This is what I currently have:

**********
Year: (IIf([DATE] Between #1/1/2001# And #12/31/2001#,"2001",IIf([DATE]
Between #1/1/2002# And #12/31/2002#,"2002")))

This line above procudes the year so that I can easily group records.

**********



Here's now the problem with the "QUARTER":

**********
Quarter: (IIf([DATE] Between #1/1# & [YEAR] And #3/31# & [YEAR],"1st"))

Based on the entered parameter -- e.g. "2001" of the date field, the year
"2001" is pulled from the date field.
I now want to automatically compute the QUARTER based on the computed YEAR
field. In the example above (QUARTER), I have done it only for the 1st
quarter. Currently -- without the "&[YEAR]", the dates default
automatically to the current year e.g. #1/1/2004#.

Again, however, that's now what I want to produce for records that are
generated in 2001. I hope this makes sense?!?!

**********



Thanks,
Tom
Click to expand...
Click to expand...
 
T

Tom Bock

Brian:

Thanks for sharing this w/ me. The year function works great!

Tom


Brian Camire said:
To find the year of a given date, use the Year function in an expression
like this:

Year([DATE])

To find the quarter of a given date, you might use the DatePart function in
an expression like this:

DatePart("q", [DATE])

In your query you want to select records from a specified year and quarter,
so enter each of the following expressions in the Field row of an empty
column in the query grid, and set the values in the criteria row in each
column to:

[YEAR]

and

[QUARTER]

respectively.

Tom Bock said:
I use a parameter query to enter the YEAR of a record.

I now would like to automatically populate the particular QUARTER for the
chosen year.


This is what I currently have:

**********
Year: (IIf([DATE] Between #1/1/2001# And #12/31/2001#,"2001",IIf([DATE]
Between #1/1/2002# And #12/31/2002#,"2002")))

This line above procudes the year so that I can easily group records.

**********



Here's now the problem with the "QUARTER":

**********
Quarter: (IIf([DATE] Between #1/1# & [YEAR] And #3/31# & [YEAR],"1st"))

Based on the entered parameter -- e.g. "2001" of the date field, the year
"2001" is pulled from the date field.
I now want to automatically compute the QUARTER based on the computed YEAR
field. In the example above (QUARTER), I have done it only for the 1st
quarter. Currently -- without the "&[YEAR]", the dates default
automatically to the current year e.g. #1/1/2004#.

Again, however, that's now what I want to produce for records that are
generated in 2001. I hope this makes sense?!?!

**********



Thanks,
Tom
Click to expand...
Click to expand...
 
T

Tom Bock

Nikos:

Ah, forgot to ask.... why do you list the quarters 3 times each in the
function?

Just want to make sure I have an understanding of this...

Thanks,
Tom


Nikos Yannacopoulos said:
Tom,

The Year one is even simpler (and quite faster for big tables):
Year: Year([Date])

For the Quarter:
Quarter:
Choose(Month([Date]),"1st","1st","1st","2nd","2nd","2nd","3rd","3rd","3rd","
4th","4th","4th")

HTH,
Nikos

Tom Bock said:
I use a parameter query to enter the YEAR of a record.

I now would like to automatically populate the particular QUARTER for the
chosen year.


This is what I currently have:

**********
Year: (IIf([DATE] Between #1/1/2001# And #12/31/2001#,"2001",IIf([DATE]
Between #1/1/2002# And #12/31/2002#,"2002")))

This line above procudes the year so that I can easily group records.

**********



Here's now the problem with the "QUARTER":

**********
Quarter: (IIf([DATE] Between #1/1# & [YEAR] And #3/31# & [YEAR],"1st"))

Based on the entered parameter -- e.g. "2001" of the date field, the year
"2001" is pulled from the date field.
I now want to automatically compute the QUARTER based on the computed YEAR
field. In the example above (QUARTER), I have done it only for the 1st
quarter. Currently -- without the "&[YEAR]", the dates default
automatically to the current year e.g. #1/1/2004#.

Again, however, that's now what I want to produce for records that are
generated in 2001. I hope this makes sense?!?!

**********



Thanks,
Tom
Click to expand...
Click to expand...
 
B

Bas Cost Budde

Tom said:
Nikos:

Ah, forgot to ask.... why do you list the quarters 3 times each in the
function?
Choose(Month([Date]),"1st","1st","1st","2nd","2nd","2nd","3rd","3rd","3rd","4th","4th","4th")
Click to expand...
Click to expand...

That's how Choose works. If the first argument evaluates to 1, the
result is the second parameter. If the first argument evaluates to 2,
the result is the third parameter. Catch the cow? Now for every 3 months
the quarter is the same.
 
T

Tom

Thanks for sharing the info w/ me.

--
Thanks,
Tom


Bas Cost Budde said:
Tom said:
Nikos:

Ah, forgot to ask.... why do you list the quarters 3 times each in the
function?
Click to expand...
Choose(Month([Date]),"1st","1st","1st","2nd","2nd","2nd","3rd","3rd","3rd","
4th","4th","4th")

That's how Choose works. If the first argument evaluates to 1, the
result is the second parameter. If the first argument evaluates to 2,
the result is the third parameter. Catch the cow? Now for every 3 months
the quarter is the same.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please help me fix this flawed query formula 7
URGENT HELP NEEDED WITH GETTING AVERAGES BY QUARTER 1
Querry by Quarter 7
grouping quarters 3
quarterly date queries generating annually 5
Calculate Start and End Date of a Quarter 8
Query/Function/Parameter Troubles... 2
Query Parameters 1

Top