WhereCondition in .OpenReport

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

Hal

How can I put the following criteria in the WhereCondition in .OpenReport?
WHERE (((Format$([Date],"\Qq
yyyy",0,0))=[Forms]![frmPrintSelectQtr]![txtCriteria]))
(The report is based on a saved query)

txtCriteria would contain one OR more "formatted" quarters like this: Q2
2003

If there is more than one quarter (criteria) how should the txtCriteria
look?
- "Q2 2003" OR "Q4 2003"
- "Q2 2003", "Q4 2003"
i.e what should the separator be?

Any help much appreciated...

TIA Hal
 
Hal said:
How can I put the following criteria in the WhereCondition in
.OpenReport? WHERE (((Format$([Date],"\Qq
yyyy",0,0))=[Forms]![frmPrintSelectQtr]![txtCriteria]))
(The report is based on a saved query)

txtCriteria would contain one OR more "formatted" quarters like this:
Q2 2003

If there is more than one quarter (criteria) how should the
txtCriteria look?
- "Q2 2003" OR "Q4 2003"
- "Q2 2003", "Q4 2003"
i.e what should the separator be?
Click to expand...

You'll have to build the whole WhereCondition string by "concatenating
in" the actual value of the text box. The easiest way to do this would
be to set up txtCriteria to contain a comma-delimited list as in your
second example; e.g.,

"Q2 2003"
"Q2 2003", "Q4 2003"
"Q2 2003", "Q4 2003", "Q1 2004"

Then you could build a where-condition like this:

Dim strLinkCriteria As String

strLinkCriteria = _
"Format([Date], '\Qq yyyy',0,0) IN (" & _
Forms!frmPrintSelectQtr!txtCriteria & _
")"

DoCmd.OpenReport "MyReport", , , stLinkCriteria
 
Thank you Dirk, but I get the following error:

Error 3073: Syntax error (missing operator) in query expression
'(Format([Date], '\Qq yyyy',0,0) IN (Q3 2003))'.

Hal

PS: I have the "field" Format$([Date],"\Qq yyyy",0,0) in the
query. It is based on the [Date] also in the query. The "Show" is not
selected.

I'm trying to limit the report to only certain quarters and a similar report
to only the selected months.
 
Hal said:
Thank you Dirk, but I get the following error:

Error 3073: Syntax error (missing operator) in query expression
'(Format([Date], '\Qq yyyy',0,0) IN (Q3 2003))'.
Click to expand...

Then your txtCriteria field doesn't contain what you said it did.
Apparently, it doesn't have the quotes around the quarter identifiers;
that is, it has

Q3 2003

not

"Q3 2003"

For this to work as written, you must have the quotes around the
quarters in txtCriteria. If you can't arrange that, then you could add
some simple code to parse out the quarters using the comma as a
delimiter.
PS: I have the "field" Format$([Date],"\Qq yyyy",0,0) in the
query. It is based on the [Date] also in the query. The "Show" is not
selected.
Click to expand...

If you clicked the Show box and gave it a name, you could use that
calculated-field name in building strLinkCriteria instead of repeating
the expression. But that would be up to you.
 
Back
Top