I am having problems with SQL statements depending on combo box selections displaying in a listbox. I have two combos... The First combo allows user to select which criteria to search by.. "AEC", "SHIPPER", "BOOKING NUMBER" & The second is the format search. Beginning with or Ending with or Exact. The only one that works correctly is EXACT match.... For instance i have AEC'S 11361A, 11361B 11361C & D.
If i enter in "11361A" it will show that record... if i enter in 11361 ( which should pull all 4 records ) it doesnt.. Syntax error below in formatsqltext???
Option Compare Database
Private Sub Command4_Click()
Select Case (ExpSearchByCombo)
Case "AEC": Call SearchByAec
Case "Shipper": Call SearchbyShipper
Case "Booking Number": Call searchbybooking
End Select
End Sub
Private Function SearchByAec()
Dim SQL As String
Dim SQLSearchText As String
SQLSearchText = FormatSQLSearchText()
MsgBox FormatSQLSearchText
SQL = "SELECT ExpRecords.ID, ExpRecords.AEC, ExpRecords.Shipper" & _
" FROM ExpRecords " & _
" WHERE (ExpRecords.AEC " & SQLSearchText & ");"
Me.List6.RowSource = SQL
Me.List6.BoundColumn = 0
Me.List6.ColumnCount = 3
Me.List6.Requery
End Function
Private Function SearchbyShipper()
End Function
Private Function searchbybooking()
Dim SQL As String
Dim SQLSearchText As String
SQLSearchText = FormatSQLSearchText()
MsgBox FormatSQLSearchText
SQL = "SELECT ExpBooking.[AEC], ExpBooking.[UltimateCarrierRef], ExpBooking.[ID] " & _
" FROM ExpBooking " & _
" WHERE ([ExpBooking].[UltimateCarrierRef] " & SQLSearchText & ");"
Me.List6.RowSource = SQL
Me.List6.BoundColumn = 1
Me.List6.ColumnCount = 3
Me.List6.Requery
End Function
Private Function FormatSQLSearchText() As String
Select Case (cboSearchFilter)
Case "Exact Match"
FormatSQLSearchText = "='" & SearchByText & "' "
Case "Beginning With"
FormatSQLSearchText = "LIKE '" & SearchByText & "%' "
Case "Ending With"
FormatSQLSearchText = "LIKE '%" & SearchByText & "' "
Case "Anywhere"
FormatSQLSearchText = "LIKE '%" & SearchByText & "%' "
Case "Custom"
FormatSQLSearchText = "LIKE '" & SearchByText & "' "
End Select
End Function
If i enter in "11361A" it will show that record... if i enter in 11361 ( which should pull all 4 records ) it doesnt.. Syntax error below in formatsqltext???
Option Compare Database
Private Sub Command4_Click()
Select Case (ExpSearchByCombo)
Case "AEC": Call SearchByAec
Case "Shipper": Call SearchbyShipper
Case "Booking Number": Call searchbybooking
End Select
End Sub
Private Function SearchByAec()
Dim SQL As String
Dim SQLSearchText As String
SQLSearchText = FormatSQLSearchText()
MsgBox FormatSQLSearchText
SQL = "SELECT ExpRecords.ID, ExpRecords.AEC, ExpRecords.Shipper" & _
" FROM ExpRecords " & _
" WHERE (ExpRecords.AEC " & SQLSearchText & ");"
Me.List6.RowSource = SQL
Me.List6.BoundColumn = 0
Me.List6.ColumnCount = 3
Me.List6.Requery
End Function
Private Function SearchbyShipper()
End Function
Private Function searchbybooking()
Dim SQL As String
Dim SQLSearchText As String
SQLSearchText = FormatSQLSearchText()
MsgBox FormatSQLSearchText
SQL = "SELECT ExpBooking.[AEC], ExpBooking.[UltimateCarrierRef], ExpBooking.[ID] " & _
" FROM ExpBooking " & _
" WHERE ([ExpBooking].[UltimateCarrierRef] " & SQLSearchText & ");"
Me.List6.RowSource = SQL
Me.List6.BoundColumn = 1
Me.List6.ColumnCount = 3
Me.List6.Requery
End Function
Private Function FormatSQLSearchText() As String
Select Case (cboSearchFilter)
Case "Exact Match"
FormatSQLSearchText = "='" & SearchByText & "' "
Case "Beginning With"
FormatSQLSearchText = "LIKE '" & SearchByText & "%' "
Case "Ending With"
FormatSQLSearchText = "LIKE '%" & SearchByText & "' "
Case "Anywhere"
FormatSQLSearchText = "LIKE '%" & SearchByText & "%' "
Case "Custom"
FormatSQLSearchText = "LIKE '" & SearchByText & "' "
End Select
End Function
Last edited: