LOST! SQL STATEMENT as recordsource to listbox on case statement (1 Viewer)

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

In this situation you want * as a wildcard, not %. Change that and see if it works.

Great! Thank you very much! That worked. Can i ask if you dont mind explaining why and when i would use the "wildcard" instead of % ? Im not familar with access.. this is my first database.

They are both wildcards, but * is used by DAO and JET/ACE (the built-in database engine). % is used by ADO.

Great thanks alot Paul! I also am looking at your website! Unfortunately i run a small business ( transportation industry as well ) & our software is on its last leg.. Rather then spend the additional funds right now i am working to write a database that will work. First one & certainly not my career... But this site has been very helpful! I appreciate your input!

You can use this to debug the SQL string. Post it here if you don't spot the problem:

http://www.baldyweb.com/ImmediateWindow.htm