multiple field query Run-time error '13': Type mismatch

[braff24]

Hi, I'm new to access and need some help with a project I'm working on. I have a database that will search through one field and I'm trying to change it so it will search through two fields and everything I try I get "Run-time error '13': Type mismatch".



Here is what I have that works perfectly.

Form_frmTools.RecordSource = "select * from Tools where " & "ToolName" & " LIKE '*" & txtSearchString & "*'"

But I want to change it so it will search through the fields ToolName and PartNumber. This is what I have now and I'm getting the error.

Form_frmTools.RecordSource = "select * from Tools where " & ("PartNumber" & " LIKE '*" & txtSearchString & "*'") Or ("ToolName" & " LIKE '*" & txtSearchString & "*'")

I've also tried this with the same result.

Form_frmTools.RecordSource = "SELECT Tools.PartNumber, Tools.ToolName, * FROM Tools WHERE (((Tools.PartNumber) Like "*" & [Forms]![frmSearch]! [txtSearchString] & "*")) OR (((Tools.ToolName) Like "*" & [Forms]![frmSearch]![txtSearchString] & "*"))"

Any help is appreciated. Thanks in advance.

[CJ_London]

I'm surprised you are getting that error since the code won't compile

"select * from Tools where " & ("PartNumber" & " LIKE '*" & txtSearchString & "*'") Or ("ToolName" & " LIKE '*" & txtSearchString & "*'")

also this does not seem right

Form_frmTools.RecordSource

I would expect to see

Me.RecordSource if the code is being run in the same form

or

Me.subformcontrolname.Form.RecordSource if this is updating a subform


or

Forms!frmTools.RecordSource if the code is in a different form (and frmTools needs to be open)

[June7]

Try:

Form_frmTools.RecordSource = "select * from Tools where PartNumber LIKE '*" & Me.txtSearchString & "*' Or ToolName LIKE '*" & Me.txtSearchString & "*';"

Form_frmTools. works just fine in VBA and it provokes intellisense popup tips. Forms! does not.

I prefer to set form Filter and FilterOn properties instead of changing the RecordSource.

[braff24]

Try:

Form_frmTools.RecordSource = "select * from Tools where PartNumber LIKE '*" & Me.txtSearchString & "*' Or ToolName LIKE '*" & Me.txtSearchString & "*';"

Form_frmTools. works just fine in VBA and it provokes intellisense popup tips. Forms! does not.

I prefer to set form Filter and FilterOn properties instead of changing the RecordSource.

Thanks! Works perfectly.

[Micron]

Not trying to be smart here, but did you learn why? I'm curious.