Syntax error in query!

  • Thread starter Thread starter hima
  • Start date Start date
H

hima

hi,

i was trying to compare an attribute (DTPanel ) of Testtable for 4
vehicles of a particular OEM,Vehicle model and model year.......


i just started off with this code...am not sure how to frame the
query....could anyone help me on th same.....plz do check the syntax of
the same........am a beginner


Option Compare Database


On Error GoTo ProcError


Dim strValueList$
Dim rs As DAO.Recordset
Dim strSQL As String, strTemp As String
Dim i As Integer
Dim count As Integer


i = 0


If chkIncludeDTPanel.Value = -1 Then


strSQL = "SELECT DTPanel FROM TestTable INNER JOIN VehicleTable" & _
" ON TestTable.DoorTrimPanelID =
VehicleTable.DoorTrimPanelID" & _
" WHERE (VehicleTable.OEM = """ & Me.cboOEM.Column(1) &
"""" & _
" AND VehicleTable.VehicleModel = """ &
Me.CboVehicleModel.Column(1) & """" & _
" AND VehicleTable.ModelYear = " &
Me.cboModelYear.Column(1) & """")
" OR ( VehicleTable.OEM = """ & Me.CboOEM2.Column(1) &
"""" & _
" AND VehicleTable.VehicleModel = """ &
Me.CboVehicleModel2.Column(1) & """" & _
" AND VehicleTable.ModelYear = " &
Me.CboModelYear2.Column(1) & """" )
" OR( VehicleTable.OEM = """ & Me.CboOEM3.Column(1) &
"""" & _
" AND VehicleTable.VehicleModel = """ &
Me.CboVehicleModel3.Column(1) & """" & _
" AND VehicleTable.ModelYear = " &
Me.CboModelYear3.Column(1) & """" )
" OR (VehicleTable.OEM = """ & Me.CboOEM4.Column(1) &
"""" & _
" AND VehicleTable.VehicleModel = """ &
Me.CboVehicleModel4.Column(1) & """" & _
" AND VehicleTable.ModelYear = " &
Me.CboModelYear4.Column(1) )


Set rs = CurrentDb.OpenRecordset(strSQL)


If Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
i = 0
count = rs.Fields.count - 1
For i = 0 To count
strTemp = strTemp & rs.Fields(i).Name & ": " &
rs.Fields(i) & " "
Next i
rs.MoveNext
Loop
Else
MsgBox "No Records"
End If
End If


txtResults.Value = strTemp


ExitProc:
' Cleanup
On Error Resume Next
rs.Close: Set rs = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdPerformSearch_Click..."
Resume ExitProc
End Sub
 
One thing that pops out immediately is that the end of the query is
incorrect.

You need the parenthesis inside of quotes:

" AND VehicleTable.VehicleModel = """ &
Me.CboVehicleModel4.Column(1) & """" & _
" AND VehicleTable.ModelYear = " &
Me.CboModelYear4.Column(1) & ")"

Why not use Debug.Print strSQL to print out the actual SQL statement (as
opposed to how you're building it) and look at it?
 
Back
Top