Re: strSQL using Like "*"

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Steven" <Steven@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BEB34F6A-44ED-4EA2-A3EA-F735F15D51C2@xxxxxxxxxxxxxxxx
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

.

Relevant Pages

  • Re: Help! Outer Join problem
    ... dateTime type. ... you could end up with a problem of string comparisons. ... query and then bring that into another query with the other two tables. ... Try the following as the FROM clause (and you should be able to drop the ...
    (microsoft.public.access.queries)
  • Re: Build an IN clause from SELECT records
    ... string would be used for an IN clause in a subsequent SQL query. ... How do I return the results of this query to a string with each record ...
    (comp.databases.ms-access)
  • Re: strSQL using Like "*"
    ... Switch the query to SQL View, and edit the WHERE clause so it looks like ... A much more efficient solution is to create the filter string dynamically, ... to use a strSQL for the recordset and what I am querying on is based on 5 ...
    (microsoft.public.access.modulesdaovba)
  • Re: strSQL using Like "*"
    ... this can be odd the first time you see it. ... Ultimately a WHERE clause is something that evaluates to True or False If it's true, the record gets included; ... Switch the query to SQL View, and edit the WHERE clause so it looks like ... A much more efficient solution is to create the filter string dynamically, ...
    (microsoft.public.access.modulesdaovba)
  • Re: issue with runing Select query with condition using code
    ... Build the whole query statement, not just the WHERE clause, and assign it like this: ... Dim strSql As String ... > Dim strsql As String ...
    (microsoft.public.access.modulesdaovba)
Loading