change criteria in query automatically

[BillyRogers]

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

 

[chris.nebinger]

When would you ever use "05"?

To get the current quarter:

Format(Now(),"Q")

So, your criteria for the quarter field would look like:

"0" & Format(Now(),"Q")


If you need more help, let me know.


Chris Nebinger

 

[BillyRogers]

aI don't need the current quarter. the numbers are buckets that represent
months that are stored as numbers in a field.

 

[chris.nebinger]

I see, you have the user type the quarter (2), and want all the records
in the 2nd quarter 2006 (Apr, May, Jun) to be pulled? In that case,
for criteria:


DatePart("q",[DateField]) = [Enter Quarter to Pull]


You will also want to restrict based on year.

Can I ask a question? Why are you saving the month in a numeric field,
and not a date field?


Chris