SQL code error :: End of sentence expected

[nhtraven]

I am having trouble getting this SQL code to work. It is saying expected end of sentence:: the cursor is hightlighting the comma after Dynamic query.

Any ideas?



If tblname1 <> &quot;&quot; Then
Set QD = (&quot;Dynamic_Query&quot;, &quot;SELECT * FROM &quot;& tblname1 & &quot; &quot; & where)


Else
Set QD = (&quot;Dynamic_Query&quot;, &quot;SELECT * FROM &quot; & tblname1 & &quot; &quot; & where)

End If

 

[Lightning]

Because of the comma, this statement is being read as two separate arguments of a statement, with the first argument consisting of the words Dynamic_Query and the second as &quot;SELECT * FROM &quot;& tblname1 & &quot; &quot; & where). Access interprets this as a string expression consisting of &quot;Dynamic_Query&quot; and expects the end of the statement at this point.

What is &quot;Dynamic_Query&quot; and exactly what are you trying to achieve with this If statement?


HTH
Lightning

 

[nhtraven]

Hi lightning
i am making a form that has all fields from two tables on it that will allow user to query on multiple criterias. I am using a command button to activate the query. Here is a cut down version of the code::


Private Sub cmdRunQuery_Click()

Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant
Dim tblname1 As String
Dim tblname2 As String

Set db = CurrentDb()

On Error Resume Next
db.QueryDefs.Delete (&quot;Dynamic_Query&quot

On Error GoTo 0

where = Null
tblname1 = tblname1
tblname2 = tblname2

'Evaluate Criteria entered
'parcel no <data type is Text >
If Left(Me![ParcelNo], 1) = &quot;*&quot; Or Right(Me![ParcelNo], 1) = &quot;*&quot; Then

where = where & &quot; AND [tblname1].[ParcelNo] like ' &quot; + Me![ParcelNo] + &quot;'&quot;

Else
where = where & &quot; AND [tblname1].[Parcelno] = ' &quot; + Me![ParcelNo] + &quot;'&quot;
End If


'Range Queries

'LotSize range
If Not IsNull(Me![LotSizeE]) Then
where = where & &quot; AND [tblname1].[LotSize] between &quot; + Me![LotSize] + &quot; AND &quot; & Me![LotSizeE]

Else
where = where & &quot; AND [tblname1].[LotSize] >= &quot; + Me![LotSize]
End If

If tblname1 <> &quot;&quot; Then
Set QD = (&quot;Dynamic_Query&quot; &quot;SELECT * FROM &quot;& tblname1 & &quot; &quot; & where)


Else
Set QD = (&quot;Dynamic_Query&quot;, &quot;SELECT * FROM &quot; & tblname1 & &quot; &quot; & where)

End If


DoCmd.OpenQuery &quot;Dynamic_Query&quot;


End Sub


I am not sure how to accomplish this. any ideas are very much appreicated

Raven

 

[nhtraven]

geesh i forgot some code:
statement should read this way

Set QD = db.CreateQueryDef(&quot;Dynamic_Query&quot;, &quot;SELECT * FROM &quot; & tblname1 & &quot; &quot; & where)

 

[Lightning]

Two suggestions:

1) Change the name of your &quot;where&quot; variable to something other than the keyword &quot;Where&quot;. This could be causing problems in reading your code. eg

strWhere = Where & &quot; AND [tblname1].[LotSize] between &quot; +
Me![LotSize] + &quot; AND &quot; & Me![LotSizeE]

2) Instead of putting the SQL string in your create query statement, set a variable to the string statement and then include the variable in the create statement. eg:

strSQLCode = &quot;SELECT * FROM &quot; & tblname1 & &quot; &quot; & strWhere)

Set QD = db.CreateQueryDef(&quot;Dynamic_Query&quot;, strSQLCode)

HTH

Lightning