Passing WHERE Statement with DoCmd.OpenReport

[crhodus]

I am trying to pass values selected from a list box to a report named 'CountyReport'. In my report, I have the following statement as my datasource:

SELECT [suggested-build/site].project_id, [suggested-build/site].[req-build_name], [suggested-build/site].[req-county], Project.project_mgr
FROM (Company_Info INNER JOIN Project ON Company_Info.company_id = Project.company_id) INNER JOIN [suggested-build/site] ON Project.project_id = [suggested-build/site].project_id
ORDER BY [suggested-build/site].[req-county], [suggested-build/site].[req-build_name];


On a form I built, I have my list box that the user can select counties from. After they have selected the appropriate counties, they click on a button to run the report.

When they click on the button located on this form, I'm creating a WHERE statement.
Example: " WHERE [suggested-build/site].[req-county] = 'SMITH' OR [suggested-build/site].[req-county] = 'JONES' "

After the WHERE statement has been created, I am using the following code to run the report:

----------------------
' This variable holds the name of the report
Dim stDocName As String

' The string strSQL holds the WHERE statement that I created.
stDocName = "CountyReport"
DoCmd.OpenReport stDocName, acViewPreview, , strSQL
----------------------

When the DoCmd is executed I receive a Run-Time error '3075'. It says "Syntax error (missing operator) in query expression '( WHERE [suggested-build/site].[req-county]= 'ADAMS' OR [suggested-build/site].[req-county]= 'JONES')'.

My questions are:
1. Is my DoCmd statement correct?
2. Can I pass a WHERE statement to a report? I assume that I can after looking at the help documentation for OpenReport.

Thanks!

 

[fuzzygeek]

Where filter

"WhereCondition Optional Variant. A string expression that's a valid SQL WHERE clause without the word WHERE." Take out the word where.

 

[crhodus]

Thanks! I didn't think this all the way through.

 

[WayneH]

I'm having a very similar and annoying problem...

I'm printing a simple whouse report.....

the report is invoked via a form where the user selects a value from a drop down list ..... this value is the criteria that I am trying to create my WHERE clause with ....

My RecordSource for the report is a simple * query :

SELECT DistWhouse.*
FROM DistWhouse;


My VBA code behind the button is:

Dim stDocName As String 'Report Name'
Dim stWhereStr As String 'Where Condition'
Dim stSection As String 'Selection from drop down list

stSection = SectionSelection.Value
stDocName = "BinReport"
stWhereStr = "[DistWhouse].[Section] ='" + stSection + "';"

DoCmd.OpenReport stDocName, acPreview, , stWhereStr

This is error:
Syntax Error in query expression '([DistWhouse].[Section] = 'S1''

I know it's creating the WHERE string correctly......just cant figure out what the syntax problem is.....?!!! I'm sure it's gonna be something so stupid I'll kick myself.......

 

[fuzzygeek]

Possible solution

Try this:
SELECT *
FROM DistWhouse;

 

[WayneH]

Nope...it didnt work......

thanks anyway thoug my fuzzy buddy...

 

[fuzzygeek]

Hummm

Try this

stWhereStr = "[DistWhouse].[Section] ='" & stSection & "';"

instead of

stWhereStr = "[DistWhouse].[Section] ='" + stSection + "';"

 

[WayneH]

no dice.....

it's definitively creating the WHERE statement correctly........ cause when I change the dropdown list value......the syntax error...is reflecting it.....


so Go figure.....

 

[fuzzygeek]

Semicolon

Remove the semicolon from the where statement...unneeded

 

[WayneH]

Rock On! My new Fuzzy Friend...!!

......... I shoulda figuerd as much.....

anyhow..Thanks a lot ....