Report Record Source

  • Thread starter Thread starter Robert5833
  • Start date Start date
R

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
 
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
 
Thank you Duane, I'll give that a try. It looks like a much simpler approach
and probably better for me.

Thanks again!

RRL

Duane Hookom said:
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


Robert5833 said:
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
Click to expand...
Click to expand...
 
Back
Top