Parameter Query - Date IIF

[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

 

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

 

[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

 

[Tom Bock]

Nikos:

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

Thanks again,
Tom

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

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

 

[Tom Bock]

Brian:

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

Tom

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.

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

 

[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

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

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

 

[Bas Cost Budde]

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

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.

 

[Tom]

Thanks for sharing the info w/ me.

--
Thanks,
Tom

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

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.