Report Criteria from Textbox

  • Thread starter Thread starter Hal
  • Start date Start date
H

Hal

How can I get a reports criteria to work if there is more than part to the
criteria.

I have a report based on a query. One of the fields are:
Format$([Date],"\Qq yyyy",0,0)
and the criteria is: [Forms]![frmPrintSelectQtr]![txtCriteria]

If I have only one criteria in txtCriteria i.e. "Q2 2003" it works fine
but if I have more than one criteria, i.e. "Q2 2003" OR "Q3 2003"
the query/Report is empty.

What am I doing wrong here?

TIA
 
I've seen this also.

The way I got around it was to use (in your scenario) 2
different Queries as possible RecordSources for the Report.

I set a Boolean depending on if one (or two) of the Dates
are entered. Based on that Boolean value, I set the
appropriate RecordSource on "Report-Open".

If you use more criteria, you use other Queries as
RecordSources.

HTH = Bob
 
The weird thing is, if I enter the criteria "Q2 2003" OR "Q3 2003" directly
in the query it works fine, but not when I do it from the textbox...

Hal
 
Basically when you have a parameter in a query like you have, it looks for
records were the field matches exactly what you entered. In other words it
is not looking for a field that is either "Q2 2003" or "Q3 2003" it is
looking for a field that is equal to "Q2 2003 OR Q3 2003" or if you are
actually entering the quotes in the text box it is looking for ""Q2 2003" OR
"Q3 2003"". If you need to have the option from more than one quarter, you
will probably need more than one text box (though there may be some other
way to do it that someone else knows of).

Sue
 
This happens because in the latter case the query uses the whole string,
"'Q2 2003' OR 'Q3 2003'"
as the criteria, and there is no record like that in your table.
You can use the contents of the text box to build the filter string for
the report in the report's Open event in VBA - this is the easiest. Omit
the word OR (make it "Q2 2003, Q3 2003"), and change the record source
to IN instead of =, and add parenthesis:

"SELECT ... FROM ... WHERE theDate IN (" &
[Forms]![frmPrintSelectQtr]![txtCriteria] & ")"

Pavel
 
Back
Top