I built a form for colleagues to use, to search a single field in one table for a single word. The SQL is
Code:
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.
Code:
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.