strSQL using Like "*"

  • Thread starter Thread starter Steven
  • Start date Start date
S

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
 
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
 
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

Allen Browne said:
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.

Steven said:
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
Click to expand...
Click to expand...
 
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.
Steven said:
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

Allen Browne said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top