Report Record Source

[Robert5833]

Good day all;
I have a report that uses a combo box control as a source for its filter.
The Row Source for the combo box is provided by a subroutine that rebuilds
the WHERE portion of the SQL expression.

All of that works fine; except for two problems. 1) the expression in VBA
doesn't filter the recordset to the value selected (whereas the SQL
expression when ran as an SQL query does), and 2) I get a syntax error when
the selected value in the combo box has spaces or hyphens (even though the
VBA SQL WHERE appears to be treating the value as "text" enclosed in
quotation marks). The subroutine generated WHERE as follows:

WHERE (tblProject.fldProjectNumber = "100100000")

I can't figure out how to bracket the returned value so it is treated as a
text string, to include spaces and other characters (which I think would
resolve the syntax error), and I don't understand why as a query criterion
the report won't filter on the value selected.

I can post other pieces of the subroutine and other strings of code if it
helps (but it's long...).

Any suggestions or help would be greatly appreciated.

RRL

 

[Duane Hookom]

I am having trouble picturing what you are doing. If you are building a where
condition to use in the DoCmd.OpenReport method, it should not include the
"WHERE" or the table name. I would expect the where condition to look like:

Dim strWhere as String
strWhere = "[fldProjetNumber]= """ & Me.cboProjectNumber & """ "
DoCmd.OpenReport "rptName", acPreview, , strWhere

 

[Robert5833]

Thank you Duane, I'll give that a try. It looks like a much simpler approach
and probably better for me.

Thanks again!

RRL

I am having trouble picturing what you are doing. If you are building a where
condition to use in the DoCmd.OpenReport method, it should not include the
"WHERE" or the table name. I would expect the where condition to look like:

Dim strWhere as String
strWhere = "[fldProjetNumber]= """ & Me.cboProjectNumber & """ "
DoCmd.OpenReport "rptName", acPreview, , strWhere

--
Duane Hookom
Microsoft Access MVP

Good day all;
I have a report that uses a combo box control as a source for its filter.
The Row Source for the combo box is provided by a subroutine that rebuilds
the WHERE portion of the SQL expression.

All of that works fine; except for two problems. 1) the expression in VBA
doesn't filter the recordset to the value selected (whereas the SQL
expression when ran as an SQL query does), and 2) I get a syntax error when
the selected value in the combo box has spaces or hyphens (even though the
VBA SQL WHERE appears to be treating the value as "text" enclosed in
quotation marks). The subroutine generated WHERE as follows:

WHERE (tblProject.fldProjectNumber = "100100000")

I can't figure out how to bracket the returned value so it is treated as a
text string, to include spaces and other characters (which I think would
resolve the syntax error), and I don't understand why as a query criterion
the report won't filter on the value selected.

I can post other pieces of the subroutine and other strings of code if it
helps (but it's long...).

Any suggestions or help would be greatly appreciated.

RRL