help in query!

B

bindu

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.....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) & """" & _
" AND VehicleTable.OEM = """ & Me.CboOEM2.Column(1) &
"""" & _
" AND VehicleTable.VehicleModel = """ &
Me.CboVehicleModel2.Column(1) & """" & _
" AND VehicleTable.ModelYear = " &
Me.CboModelYear2.Column(1) & """" & _
" AND VehicleTable.OEM = """ & Me.CboOEM3.Column(1) &
"""" & _
" AND VehicleTable.VehicleModel = """ &
Me.CboVehicleModel3.Column(1) & """" & _
" AND VehicleTable.ModelYear = " &
Me.CboModelYear3.Column(1) & """" & _
" AND 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
 
W

Wolfgang Kais

Hello bindu.

bindu said:
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.....am a beginner


Option Compare Database
Click to expand...

Also use: Option Explicit
On Error GoTo ProcError
Click to expand...

The Sub statement is missing
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) & """" & _
" AND VehicleTable.OEM = """ & Me.CboOEM2.Column(1) & """" & _
Click to expand...

[... repeated with 4 groups of controls: 2, 3, 4]
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
Click to expand...

I guess that you want to see the values of 4 records but see nothing.
Your query has all 4 groups of criteria linked via AND, but there is
no record that matches all 4 criteria.
Build the query like this:
Select ... From ... Inner Join ... On ...
Where
(VehicleTable.OEM=...
AND VehicleTable.VehicleModel=...
AND VehicleTable.ModelYear =...) OR
(....) OR (....) OR (....)
 
H

hima

This way right wolfgang,

can u check the synatx..

"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) & """"
& _
" OR VehicleTable.VehicleModel = """ &
Me.CboVehicleModel2.Column(1) & """" & _
" OR 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)
 
W

Wolfgang Kais

Hello hima.
can u check the synatx..

"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) & """"
& _
" OR VehicleTable.VehicleModel = """ &
Me.CboVehicleModel2.Column(1) & """" & _
" OR 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)
Click to expand...

You sould use brackets (although AND is evaluated before OR),
you forgot some quotation matks and you used too many ORs.
It sould look like this:

"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) & """)"
 
H

hima

I get an error which says data type mismatch in criteria
expression....can anyone help me on th same...

Option Compare Database
Option Explicit

Private Sub CmdPerformCompare_Click()


Dim strValueList$
Dim rs As DAO.Recordset
Dim strSQL As String, strTemp As String
Dim j As Integer
Dim count As Integer
j = 0

If chkDTPanel.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) & """)"

Debug.Print strSQL

Set rs = CurrentDb.OpenRecordset(strSQL)

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


txtResults.Value = strTemp

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax error in query! 1
Error ! 1
Issues in access 2
can anyone help me plzz 10
problem in the code 6
remove duplicate email addresses on bulk email 1
"Undefined Function" in Expression when using VBA module in query 1
Problems with a query; result to textbox. 2

Top