Trying to query a table using multiple values in a textbox on a form - Not Working

[AJM2013]

I have a database in Microsoft access 2010. The database has a table that stores prospective customer records, and a form that is used to input a search criteria(s) via textboxes, which then queries prospective customers table and returns the records that contain the inputted search criteria(s).

An Example of Textboxes values on the search criteria form:
Textbox - name: bob
Textbox - address: Left blank
Textbox - category: car,boat,truck

I Have tried creating a query with the following

field: name
criteria: like “*” & name & “*”
field: address
criteria: like “*” & address & “*”
field: category
criteria: like “*” & category & “*”

SQL code:
SELECT customerName ,address,category
FROM prospectiveCustomers
WHERE customerName LIKE “*” & name & “*” AND address LIKE “*” & address& “*” AND category LIKE “*” & category& “*”;

That works, but only for one value in a textbox. Once there is more than one value in a textbox (e.g name: bob,smith), the query returns no records.

I have also tried splitting the values using the comma as a delimmter, then inserting the values into a new table. That is fine until one of the search criteria textboxes has been left blank. So the query I created will run, but returns no records.

SQL CODE:
SELECT prospectiveCustomers.name, prospectiveCustomers.address,prospectiveCustomers. category
FROM prospectiveCustomers, [SearchCriteria-name], [SearchCriteria-address],[SearchCriteria-category]
WHERE prospectiveCustomers.name Like [SearchCriteria-name].name AND prospectiveCustomers.address LIKE [SearchCriteria-address].address AND prospectiveCustomers.category LIKE [SearchCriteria-address].category;

I have rattled my brain, anyone got any ideas? Hopefully I have explained clearly. :-(

[June7]

Is it possible for records to not have data in any of those fields? Need to handle the nulls.

If you want to use parameterized query, here is example but it doesn't deal with nulls: http://datapigtechnologies.com/flash...tomfilter.html

Two ways to deal with the nulls

1. ([name] Like Forms![SearchCriteria-name].[name] & "*" Or [name] Is Null) AND (address LIKE Forms![SearchCriteria-address].address & "*" Or address Is Null) AND (category LIKE Forms![SearchCriteria-address].category & "*" Or category Is Null)

2. expressions in query to convert null to string and apply wildcard filter criteria under those constructed fields
Nz([name],"")
or
Nz([name],"none")

[AJM2013]

Thanks for your reply. Your advice worked . Sometimes you need someone else perspective. I ended up using NZ([name],"*") to insert the wildcard *. So when the search criteria in the text box was left blank (null) on the form, it will insert the * wildcard into the search criteria table, giving me the results I was looking for. I have posted my code below used in access 2010.

VBA CODE:

Public Function suppliedSearchCriteria()

DoCmd.RunSQL ("DELETE * FROM [searchCriteria-name] ")
Call insertSearchCriteraiaValues(Nz(Me.name.Value, "*"), "NAME", "SearchCriteria-name")

DoCmd.RunSQL ("DELETE * FROM [searchCriteria-address] ")
Call insertSearchCriteraiaValues(Nz(Me.address.Value, "*"), "ADDRESS", "SearchCriteria-address")

DoCmd.RunSQL ("DELETE * FROM [searchCriteria-category] ")
Call insertSearchCriteraiaValues(Nz(Me.category.Value, "*"), "CATEGORY", "SearchCriteria-category")

End Function

Public Function insertSearchCriteraiaValues(searchCriteriaValues As String, columnName As String, tableName As String)

Dim searchValue As String
Dim temp() As String

temp = Split(searchCriteriaValues, ",")
DoCmd.RunSQL ("DELETE * FROM [" & tableName & "] ")

For i = LBound(temp) To UBound(temp)
searchValue = "'" + Trim(temp(i)) + "'"
DoCmd.RunSQL ("INSERT INTO [" & tableName & "] (" & columnName & ")VALUES (" & searchValue & ")")
Next i

End Function

SQL QUERY CODE:

SELECT CUSTOMERS.ID, CUSTOMERS.[NAME], CUSTOMERS.ADDRESS, CUSTOMERS.CATEGORY
FROM CUSTOMERS, [SearchCriteria-name], [SearchCriteria-address], [SearchCriteria-category]
WHERE (((CUSTOMERS.[NAME]) Like "*" & [SearchCriteria-name]![NAME] & "*") AND ((CUSTOMERS.ADDRESS) Like "*" & [SearchCriteria-address]![ADDRESS] & "*") AND ((CUSTOMERS.CATEGORY) Like "*" & [SearchCriteria-category]![CATEGORY] & "*")
ORDER BY CUSTOMERS.[NAME];