Searching with multiple criteria

[keith2511]

I built a form for colleagues to use, to search a single field in one table for a single word. The SQL is

HAVING (((All_Invoices.Product_Description) Like "*" & [Forms].[Invoice_SearchForm].[EnteredText] & "*"))

My colleagues now want to search for up to five words at the same time and my initial thought was to add another four text boxes to the form. This works if all five text boxes contain text but if any are left blank the query returns all of the data in the table field.

I have had a suggestion that amending my code to insert an additional leading and trailing quotation marks i.e.

HAVING (((All_Invoices.Product_Description) Like ""*" & [Forms].[Invoice_SearchForm].[EnteredText] & "*""))

and getting the user to add multiple criteria to the text box, delimited by an asterix would work i.e. apple*orange*pear

but this produces a Data type mismatch in criteria expression error.

Grateful for any suggestions on how I can achieve my goal.

[Welshgasman]

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?


Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format


Numbers do not need anything


Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.


Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

[xps35]

You do need 5 textboxes. The idea is to build a condition string depending on which are not blank. In short:

Dim condition as string

condition = "like *" & txt1 & "*"
 
If not IsNull txt2 then
   condition =condition & " Or like *" & txt2 & "*" 
EndIf

If not IsNull txt3 then
   condition =condition & " Or like *" & txt3 & "*"
EndIf

. 
. 
.

[Welshgasman]

I would also be using a WHERE clause, not HAVING?

https://www.geeksforgeeks.org/differ...clause-in-sql/

[isladogs]

I would also be using a WHERE clause, not HAVING?

https://www.geeksforgeeks.org/differ...clause-in-sql/

Agreed. See also Speed Comparison Tests 4 (isladogs.co.uk)

[journeyman]

<<Removed and replaced>>

[journeyman]

You do need 5 textboxes.

You can also possibly use Split function so that you could search on all words separated by a space within 1 text box. Save yourself from making 5 text boxes cluttering up the place.

Something like... (Air code - not tested)

Dim mySplitVariant as variant
Dim condition as string
Dim i as integer

     MySplitVariant = Split(me.txtSearch, " ")     
     condition = "like *" & MySplitVariant(0) & "*"
     For i = 1 to ubound(mysplitVariant)
          condition =condition & " Or like *" & MySplitVariant(i) & "*"
     Next i