Filter code

[IanT]

Can anyone advise what code could be used to select ctiteria, selected in a form from a query, then produce a report relating to the crtiteria ie Month!!

 

[David R]

A couple options:
1) An unbound combo box/list box. Choose the month/months you want, then open your report using that field as a criteria.
2) Use a Calendar Control to select the timeframe you want.

HTH,
David R

 

[David Mack]

I agree. Set up a combo, list, or option group on an unbound form. Lets call the form "frmCriteria" and the combo box "cboMonth".

In your query in the QBE view, you must have a column named month or whatever. You may have to use the format function to change the date into the month format.

Now, while in the QBE (your query in design view) in the date column you have formated to month or whatever (as long as it matches cboMonth) Enter the following in the Criteria row:

Like [Forms]![frmCriteria]![cboMonth] & "*"

What this will do is pump the value selected in your combo/list/option group as a filter criteria in you query. If nothing is selected in cboMonth, the concatenated * wildcard will be passed into the query and all records will be passed.

You may do this on as many fields in the query as you desire.

So, if your combo box has "October" in it, the data in that column in the QBE has better be "October" too.

Passing the values of the frmCriteria into a sql statement in vba also works fine, but the query may be faster since it is compiled.

Cheers,

Dave

[This message has been edited by David Mack (edited 02-27-2002).]