Query Dates via form

[Guest]

Hi

I have a date field in a query "appDate", and also I have created a form to
select the quarter and year.

A drop down box "cmbMonth" gives the options of "January, February, March"
and "April, May, June" etc for the quarters and another drop down box
"cmbYear" selcts the year number.

How can i select the relevant 3 months and year in the query using the form?

Thanks

 

[Allen Browne]

So you need the quarter and year turned into the date range for the quarter.

Set these properties for your combo:
Row Source Type Value List
Row Source: 0;"Jan - Mar";3;"Apr - Jun";6;"Jul - Sep";9;"Oct -
Dec"
Column Count 2
Column Widths 0

Now in the Criteria row under your date field in the query, enter an
expression like this (all on one line):

= DateSerial(Nz([Forms].[Form1].[cmbYear], Year(Date()),

Nz([Forms].[Form1].[cmbMonth],0), 1)
AND < DateSerial(Nz([Forms].[Form1].[cmbYear], Year(Date()),
Nz([Forms].[Form1].[cmbMonth],0)+3, 1)

How it works
- DateSerial() turns the numbers into a date.

- DateSerial() can't cope with nulls, so we use Nz() to supply the current
year when cmbYear is null, and the first quarter when cmbMonth is null.

- Dates can have a time component, so we ask for less than the first of the
next quarter rather than using the Between operator.