Incorrect SQL statement for a report

[Joanne]

Is there any reason why the following statement should not turn up any hits?
There is a parties-appellant with 'test' as the only entry in it. Help!

Public Sub btnSearch_Click()
Dim SQL As String
SQL = "SELECT tblPatentCaseInformation.Citation," _
& "tblPatentCaseInformation.CaseName,tblPatentCaseInformation." _
& "[Parties-Appellant]from tblPatentCaseInformation " _
& "WHERE (((tblPatentCaseInformation.[Parties-Appellant])='test'));"


DoCmd.Echo False
DoCmd.OpenReport "CaseInformation", acViewDesign
Reports("CaseInformation").RecordSource = SQL
DoCmd.Close , , acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview
DoCmd.Echo True

 

[Duane Hookom]

I would expect to see a space befor "from...".

Dim SQL As String
SQL = "SELECT Citation, CaseName,[Parties-Appellant] " & _
"From tblPatentCaseInformation " & _
"WHERE [Parties-Appellant]='test'"
DoCmd.Echo False
DoCmd.OpenReport "CaseInformation", acViewDesign
Reports("CaseInformation").RecordSource = SQL
DoCmd.Close , , acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview
DoCmd.Echo True

I would actually not modify the report like this. I would prefer to send a
where clause in the DoCmd.OpenReport method or change the SQL property of a
saved query. Use the saved query as the record source of the report.

 

[Joanne]

Thank you very much. I did as you suggested and set up the report properties
to be based on a query. It worked! However, now that I have that working, I
have to change one of the query condition to a variable - what the user typed
into the form text box. Is it possible to use a variable from a text box in a
query?

Thank you so much for any help you can provide.

I would expect to see a space befor "from...".

Dim SQL As String
SQL = "SELECT Citation, CaseName,[Parties-Appellant] " & _
"From tblPatentCaseInformation " & _
"WHERE [Parties-Appellant]='test'"
DoCmd.Echo False
DoCmd.OpenReport "CaseInformation", acViewDesign
Reports("CaseInformation").RecordSource = SQL
DoCmd.Close , , acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview
DoCmd.Echo True

I would actually not modify the report like this. I would prefer to send a
where clause in the DoCmd.OpenReport method or change the SQL property of a
saved query. Use the saved query as the record source of the report.

--
Duane Hookom
MS Access MVP

Is there any reason why the following statement should not turn up any
hits?
There is a parties-appellant with 'test' as the only entry in it. Help!

Public Sub btnSearch_Click()
Dim SQL As String
SQL = "SELECT tblPatentCaseInformation.Citation," _
& "tblPatentCaseInformation.CaseName,tblPatentCaseInformation." _
& "[Parties-Appellant]from tblPatentCaseInformation " _
& "WHERE (((tblPatentCaseInformation.[Parties-Appellant])='test'));"


DoCmd.Echo False
DoCmd.OpenReport "CaseInformation", acViewDesign
Reports("CaseInformation").RecordSource = SQL
DoCmd.Close , , acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview
DoCmd.Echo True

 

[Duane Hookom]

You are building the SQL. Why not include the variable in the SQL?

--
Duane Hookom
MS Access MVP

Thank you very much. I did as you suggested and set up the report
properties
to be based on a query. It worked! However, now that I have that
working, I
have to change one of the query condition to a variable - what the user
typed
into the form text box. Is it possible to use a variable from a text box
in a
query?

Thank you so much for any help you can provide.

I would expect to see a space befor "from...".

Dim SQL As String
SQL = "SELECT Citation, CaseName,[Parties-Appellant] " & _
"From tblPatentCaseInformation " & _
"WHERE [Parties-Appellant]='test'"
DoCmd.Echo False
DoCmd.OpenReport "CaseInformation", acViewDesign
Reports("CaseInformation").RecordSource = SQL
DoCmd.Close , , acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview
DoCmd.Echo True

I would actually not modify the report like this. I would prefer to send
a
where clause in the DoCmd.OpenReport method or change the SQL property of
a
saved query. Use the saved query as the record source of the report.

--
Duane Hookom
MS Access MVP

Is there any reason why the following statement should not turn up any
hits?
There is a parties-appellant with 'test' as the only entry in it.
Help!

Public Sub btnSearch_Click()
Dim SQL As String
SQL = "SELECT tblPatentCaseInformation.Citation," _
& "tblPatentCaseInformation.CaseName,tblPatentCaseInformation." _
& "[Parties-Appellant]from tblPatentCaseInformation " _
& "WHERE (((tblPatentCaseInformation.[Parties-Appellant])='test'));"


DoCmd.Echo False
DoCmd.OpenReport "CaseInformation", acViewDesign
Reports("CaseInformation").RecordSource = SQL
DoCmd.Close , , acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview
DoCmd.Echo True