Parameter Query - Date IIF

  • Thread starter Thread starter Tom Bock
  • Start date Start date
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
 
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,

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
 
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...
 
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...
 
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...
 
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.
 
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...
 
Back
Top