DoCmd.OpenReport

[Gail]

Hi All,

I'm having a problem with DoCmd.OpenReport.

I have a report rptIncidents based on a query
(qryIncidentRpt) which joins two tables -tblIncident and
tblInjury (note all Incidents have a related Injury). The
report works fine by itself.

I want to set up a form to make choices via combo boxes as
to what data is reported ie only incidents on a particular
campus, or for a particular Faculty, or between certain
dates or all of the above.

If I use

DoCmd.OpenReport stDocName, acViewPreview I get
all the data (as expected)
DoCmd.OpenReport stDocName, acViewPreview, , Campus =
cboCampus NO data, no error
DoCmd.OpenReport stDocName, acViewPreview, , Campus = Me!
cboCampus NO data, no error
DoCmd.OpenReport stDocName, acViewPreview, qryIncidentRpt,
Campus = Me!cboCampus NO data, no error
DoCmd.OpenReport stDocName, acViewPreview, qryIncidentRpt,
Campus = cboCampus NO data, no error
DoCmd.OpenReport stDocName, acViewPreview, qryIncidentRpt,
Campus = Me!cboCampus NO data, no error

Syntax
DoCmd.OpenReport reportname[, view][, filtername][,
wherecondition]

Also tried with Faculty instead of Campus but same deal

I could launch into writing my own Case statements and
altering the SQL statement accordingly however, this
appeared a much easier shorter option. If only someone
can help with what I'm doing wrong.

Cheers
Gail

 

[Chris]

If Campus is a text value:

DoCmd.OpenReport stDocName, acViewPreview, , "Campus ='" &
cboCampus & "'"

If Campus is a number:

DoCmd.OpenReport stDocName, acViewPreview, , "Campus = " &
cboCampus


Chris

 

[fredg]

Hi All,

I'm having a problem with DoCmd.OpenReport.

I have a report rptIncidents based on a query
(qryIncidentRpt) which joins two tables -tblIncident and
tblInjury (note all Incidents have a related Injury). The
report works fine by itself.

I want to set up a form to make choices via combo boxes as
to what data is reported ie only incidents on a particular
campus, or for a particular Faculty, or between certain
dates or all of the above.

If I use

DoCmd.OpenReport stDocName, acViewPreview I get
all the data (as expected)
DoCmd.OpenReport stDocName, acViewPreview, , Campus =
cboCampus NO data, no error
DoCmd.OpenReport stDocName, acViewPreview, , Campus = Me!
cboCampus NO data, no error
DoCmd.OpenReport stDocName, acViewPreview, qryIncidentRpt,
Campus = Me!cboCampus NO data, no error
DoCmd.OpenReport stDocName, acViewPreview, qryIncidentRpt,
Campus = cboCampus NO data, no error
DoCmd.OpenReport stDocName, acViewPreview, qryIncidentRpt,
Campus = Me!cboCampus NO data, no error

Syntax
DoCmd.OpenReport reportname[, view][, filtername][,
wherecondition]

Also tried with Faculty instead of Campus but same deal

I could launch into writing my own Case statements and
altering the SQL statement accordingly however, this
appeared a much easier shorter option. If only someone
can help with what I'm doing wrong.

Cheers
Gail

The Where clause argument MUST be a string, therefore it must be
within quotes.

What datatype is the bound column of the cboCampus combo box?
If it is a NUMBER datatype:

DoCmd.OpenReport stDocName, acViewPreview, , "[Campus] = " &
[cboCampus]

The above where clause, assuming the value of the bound column is 5
will read "[Campus] = 5"

However, if the bound column of cboCampus is Text datatype, then:

DoCmd.OpenReport stDocName, acViewPreview, , "[Campus] = '" &
[cboCampus] & "'"

The above where clause argment, assuming the bound column value was
"Ohio" would read "[Campus] = 'Ohio'"

Notice the difference in the placement of the single and double quotes
in the instance where the bound column is text.

See Access Help on
Where clause + Restrict data to a subset of records
-
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

 

[Graham Mandeno]

Hi Gail

The 4th argument of OpenReport (WhereCondition) expects a string, so you
need something like this:

DoCmd.OpenReport stDocName, acViewPreview, , _
"Campus = " & cboCampus

What you had (Campus = cboCampus) is taking your variable (textbox? field?)
named Campus and comparing it with the value of cboCampus to see if they are
equal. Presumably they are not, so the result is False, and so you are
*asking* your report to return no data!

 

[Peter Hoyle]

Hi Gail,

Syntax for opening reports is
DoCmd.OpenReport stDocName, acViewPreview, , strCriteria

The strCriteria bit needs to be formed as a string.
Also any of the criteria that are initially strings need to be enclosed in
single quotes. (Numbers don't need this).
The value you are using has to be outside the quotes otherwise it would not
be found.

So you could have
DoCmd OpenReport stDocName, acViewPreview,,"Campus=" & "'" & Me.cboCampus &
"'"

Note the single quotes in between the double quotes,

Cheers,
Peter

 

[Guest]

Thanks guys,

Should have know, I've argued with strings and combo boxes
before.

Gail