VBA error with Keyword Searchbox

[Dyee4613]

Hi. I tried to post this in a different forum but it didn't look like my post went through and this looks more like the appropriate forum. I am trying to create a search box that allows me to filter data for multiple rows with whatever data was entered. I used this video as reference. https://www.youtube.com/watch?v=N0X8Hg-Sm6A&t=46s



I was successfully able to do this for items so I tried to expand it. Here is my code.

Option Compare Database
Option Explicit


Private Sub BtnSearch_Click()
Dim SQL As String
SQL = "SELECT Assets.ID, Assets.Truck, Assets.Yard, Assets.Time, Assets.Program, Assets.VehicleCondition, Assets.Defects" _
& "FROM Assets " _
& "Where [Truck] LIKE '*" & Me.TxtKeywords & "*' " _
& "OR [Yard] LIKE '*" & Me.TxtKeywords & "*' " _
& "OR [Program] LIKE '*" & Me.TxtKeywords & "*' " _
& "OR [VehicleCondition] LIKE '*" & Me.TxtKeywords & "*' " _


Me.SubCustomerList.Form.RecordSource = SQL
Me.SubCustomerList.Form.Requery








End Sub

The error I get ts "Run-time error '3075':

Syntax error (missing operator) in query expression 'Assets.DefectsFROM Assets Where [Truck] Like '*B91* OR [Yard] LIKE '*B91*' OR [Program] LIKE '*B91' OR [VehicleCondition] LIKE '*B91*. When I click debug, it highlights the bolded line of code (Me.SubCustomerList.Form.RecordSource = SQL)

Thanks for your help!

[orange]

The syntax error is at

Assets.DefectsFROM

you need a space before FROM.

When Access highlights an error, it can be in the line or structure just before the highlighted line.

Good luck and welcome to the forum.

[Dyee4613]

Wow. What a silly error. It's working perfectly now. Thank you!

[CJ_London]

My personal preference when building sql strings is to put the space at the beginning of the line, rather than the end of the preceding line - becomes much more obvious whether or not you have included a space

[ssanfu]

It looks like you also have an unneeded line continuation character after the last "Or"

& "OR [VehicleCondition] LIKE '*" & Me.TxtKeywords & "*' " _ <<-- right here

Be aware that "SQL" is a reserved word in Access and shouldn't be used for object/variable names.
See http://allenbrowne.com/AppIssueBadWord.html#S

"Time" is also a reserved wordin Access and shouldn't be used for object/variable names.
See http://allenbrowne.com/AppIssueBadWord.html#T



I use "sSQL".


I also put the spaces at the beginning, but use a different method for building the SQL.

sSQL = "SELECT Assets.ID, Assets.Truck, Assets.Yard, Assets.Time, Assets.Program, Assets.VehicleCondition, Assets.Defects"
sSQL = sSQL & " FROM Assets"
sSQL = sSQL & " Where [Truck] LIKE '*" & Me.TxtKeywords & "*'"
sSQL = sSQL & " OR [Yard] LIKE '*" & Me.TxtKeywords & "*'"
sSQL = sSQL & " OR [Program] LIKE '*" & Me.TxtKeywords & "*'"
sSQL = sSQL & " OR [VehicleCondition] LIKE '*" & Me.TxtKeywords & "*';"

This makes it easier (for me) to comment out or add an option.

Instead of changing the form/sub form record source all of the time, I will set a filter, which is better IMHO. Just sayin....