Pass parameter (from a form) to a report query

[sarahb845]

I am trying to pass a combobox parameter from a form to a query, which then runs the report. Generally I have not had a problem with this, until now.

Form:
The combobox, called [cboOverdue], has a Row Source Type of Value List, with "No" and "Yes" as the 2 items in the Row Source. I have set the default value is "No".

Basically, the parameter is "Only include overdue reviews?". If the user selects "Yes", then I only want the overdue performance reviews; if the user selects "No", I want the report to show ALL reviews.

When I select "Yes" in the [cboOverdue] combobox, the report correctly shows only the records with overdue reviews. However, when I select "No", I get no data on the report.


Query:
The data for the field [ReviewOverdue] is either " " or "Yes".

Tried this criteria for the field:
IIF([Forms]![frmReports]![frmReportsSUB].[Form]![cboOverdue] = "No", "*", [Forms]![frmReports]![frmReportsSUB].[Form]![cboOverdue])

I have also tried:
IIF([Forms]![frmReports]![frmReportsSUB].[Form]![cboOverdue] = "Yes", [Forms]![frmReports]![frmReportsSUB].[Form]![cboOverdue])


I'm not sure what else to try???

 

[RichMorrison]

You wrote
<<
Basically, the parameter is "Only include overdue reviews?". If the user selects "Yes", then I only want the overdue performance reviews; if the user selects "No", I want the report to show ALL reviews.

When I select "Yes" in the [cboOverdue] combobox, the report correctly shows only the records with overdue reviews. However, when I select "No", I get no data on the report.
>>

So, you want either "Yes" in column something or everything.

In that case a simple but inflexible solution is:
2 queries and 2 reports,
one query selects "Yes" the other selects everything.


RichM

 

[Pat Hartman]

Remove the criteria from the report's query. Then use the Where arguement of the OpenReport method to pass a parameter if you need one.
Dim stDocName As String
Dim stWhere As String

stDocName = "YourReport"
stWhere = "[cboOverdue] = " & Me.cboOverdue

If Me.cboOverdue = True Then
DoCmd.OpenReport stDocName, acPreview, , stWhere
Else
DoCmd.OpenReport stDocName, acPreview
End If

 

[sarahb845]

I have more parameters than just that one.

Should I add those to the OpenReport method WHERE, or leave them in the query as is????

 

[Pat Hartman]

The Where clause should look just like a where clause in any query:

stWhere = "[cboOverdue] = " & Me.cboOverdue & " AND SomeOtherField = " & Me.someotherfield

If the field is text it needs to be surrounded with single quotes:

stWhere = "[cboOverdue] = " & Me.cboOverdue & " AND SomeOtherField = '" & Me.someotherfield & "'"

 

[jdjewell]

On a related topic

I have a query for a report, as well. I decided to build the where clause as has been noted here. Now, when I attempt to open the report, I get a prompt asking me for the value for the criterion I just set up!

It's like this:

stWhere = "[tbl].[field1] = " & lstThis.value
DoCmd.OpenReport rptName, acViewPreview, , stWhere

The prompt is asking for tbl.field1

Thanks in advance.

 

[bradcccs]

If field1 is the correct name, then you should try:

stWhere = "[field1] = " & lstThis.value 
DoCmd.OpenReport rptName, acViewPreview, , stWhere

Brad.

 

[jdjewell]

[field1] has to be qualified, since there are multiple tables being joined in the query.

 

[Pat Hartman]

Remove the duplicate fields or assign the duplicates unique names.

Select tbl1.fld1 As field1a, tbl2.fld1 as field1b, ....