automate criteria changes

[Guest]

i have a query where the criteria row depends on the current date.
it needs to show

"01" or "02" or "03"
in the criteria row if the current date is in the 2nd quarter of the year and

"01" or "02" or "03" or "04" or "05" or "06"
if we are in the 3rd quarter of the year etc....

I tried to build a function in a module that uses month(Date) and based that
a select case statement, but nothing seemd to work. this is query where
someone has to type these values in by hand when they run the query every
quarter and i wanted to automate this part.

Thanks

 

[Marshall Barton]

i have a query where the criteria row depends on the current date.
it needs to show

"01" or "02" or "03"
in the criteria row if the current date is in the 2nd quarter of the year and

"01" or "02" or "03" or "04" or "05" or "06"
if we are in the 3rd quarter of the year etc....

I tried to build a function in a module that uses month(Date) and based that
a select case statement, but nothing seemd to work. this is query where
someone has to type these values in by hand when they run the query every
quarter and i wanted to automate this part.

Let's back up and look at the whole issue here.

It sort of looks like you are checking for months in a
fiscal year that starts in April. I'm not even sure that
matters. Whatever, how about using this instead of that
awkward criteria:

<= Choose(DatePart("q", Date()), "03", "06")

 

[Michel Walsh]

Hi,


Another solution is to make a table:


quarter allow ' fields name
1 "01"
1 "02"
1 "03"
2 "01"
....
2 "06"
3 "01"
....
3 "09"
4 "01"
....
4 "12" ' data


and make an inner join:



SELECT a.*
FROM a INNER JOIN tempTable ON a.fieldName = tempTable.allow
WHERE tempTable.quarter = [which quarter]




Hoping it may help,
Vanderghast, Access MVP