strSQL using Like "*"

[Steven]

I have 5 fields in a table that I want to query using Like "*". And I want
to use a strSQL for the recordset and what I am querying on is based on 5
corresponding texboxes in a form. Now the issue is that I may not be
querying in all the fields for a particular query run...it could be any
combination. ie I might be querying on Field1 and 5 ; or 1 2 3 4 5 ; or 1 3
5 ...etc may combinations. Also I have found in the query that if a field is
null then Like "*" will not return that record.

How can I set up a strSQL that will handle the issue that if for example I
put text in the textbox for Field 1 3 5 that it will know also accept Fields
2 and 4 whatever the value is. I dont want to have to build a string for
every combination of Field possibile combinations. Can it be done in all one
string?

Thank you,

Steven

 

[Allen Browne]

As you found, Like "*" doesn't cut it for nulls. Null doesn't match
anything, so you need to craft the WHERE clause so that it does not compare
to a field at all.

Switch the query to SQL View, and edit the WHERE clause so it looks like
this:

WHERE (([Forms].[Form1].[Combo1] Is Null)
OR ([Field1] = ([Forms].[Form1].[Combo1]))
AND (([Forms].[Form1].[Combo2] Is Null)
OR ([Field2] = ([Forms].[Form1].[Combo2]))
AND ((...

The bracketing is important when you mix ANDs and ORs.

A much more efficient solution is to create the filter string dynamically,
in code, from the boxes where the user entered a value. For an example of
how to do that, download the sample database from this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[Steven]

That is an interesting. I see what you are doing but it has for me an
unusual aspect of the first part not being = to something.

Thank you for your help.

Steven

As you found, Like "*" doesn't cut it for nulls. Null doesn't match
anything, so you need to craft the WHERE clause so that it does not compare
to a field at all.

Switch the query to SQL View, and edit the WHERE clause so it looks like
this:

WHERE (([Forms].[Form1].[Combo1] Is Null)
OR ([Field1] = ([Forms].[Form1].[Combo1]))
AND (([Forms].[Form1].[Combo2] Is Null)
OR ([Field2] = ([Forms].[Form1].[Combo2]))
AND ((...

The bracketing is important when you mix ANDs and ORs.

A much more efficient solution is to create the filter string dynamically,
in code, from the boxes where the user entered a value. For an example of
how to do that, download the sample database from this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have 5 fields in a table that I want to query using Like "*". And I want
to use a strSQL for the recordset and what I am querying on is based on 5
corresponding texboxes in a form. Now the issue is that I may not be
querying in all the fields for a particular query run...it could be any
combination. ie I might be querying on Field1 and 5 ; or 1 2 3 4 5 ; or 1
3
5 ...etc may combinations. Also I have found in the query that if a field
is
null then Like "*" will not return that record.

How can I set up a strSQL that will handle the issue that if for example I
put text in the textbox for Field 1 3 5 that it will know also accept
Fields
2 and 4 whatever the value is. I dont want to have to build a string for
every combination of Field possibile combinations. Can it be done in all
one
string?

Thank you,

Steven

 

[Allen Browne]

Yes, this can be odd the first time you see it.

Ultimately a WHERE clause is something that evaluates to True or False (or
possibly Null.) If it's true, the record gets included; otherwise it's
excluded. So, you can craft the expression to return True for all records,
and they all get returned.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

That is an interesting. I see what you are doing but it has for me an
unusual aspect of the first part not being = to something.

Thank you for your help.

Steven

As you found, Like "*" doesn't cut it for nulls. Null doesn't match
anything, so you need to craft the WHERE clause so that it does not
compare
to a field at all.

Switch the query to SQL View, and edit the WHERE clause so it looks like
this:

WHERE (([Forms].[Form1].[Combo1] Is Null)
OR ([Field1] = ([Forms].[Form1].[Combo1]))
AND (([Forms].[Form1].[Combo2] Is Null)
OR ([Field2] = ([Forms].[Form1].[Combo2]))
AND ((...

The bracketing is important when you mix ANDs and ORs.

A much more efficient solution is to create the filter string
dynamically,
in code, from the boxes where the user entered a value. For an example of
how to do that, download the sample database from this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

I have 5 fields in a table that I want to query using Like "*". And I
want
to use a strSQL for the recordset and what I am querying on is based on
5
corresponding texboxes in a form. Now the issue is that I may not be
querying in all the fields for a particular query run...it could be any
combination. ie I might be querying on Field1 and 5 ; or 1 2 3 4 5 ;
or 1
3
5 ...etc may combinations. Also I have found in the query that if a
field
is
null then Like "*" will not return that record.

How can I set up a strSQL that will handle the issue that if for
example I
put text in the textbox for Field 1 3 5 that it will know also accept
Fields
2 and 4 whatever the value is. I dont want to have to build a string
for
every combination of Field possibile combinations. Can it be done in
all
one
string?