Use * in SQL if Text Box is empty

[Guest]

Hello,

I have an SQL Statement that have several text boxes in the WHERE clause. Is
there a simple way of making Access ignore the text boxes that are empty and
just use the populated list boxes in the WHERE clause.

I can do this using a myriad of if statements, but there has to be a better
way.

I thought I could do this by using the NZ function and putting in * when a
text box was empty, but Access doesnt like the resulting = '*'.

Baard

 

[Allen Browne]

The "better way" is to build the SQL statement (or Filter string) from only
those boxes that have a value. It certainly avoids the mammoth nested IIf()s
and also the problem of the records that don't show up where the field is
null.

For an downloadable example of how to build such a search form, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[Douglas J. Steele]

= "*" means return those rows where that field contains exactly *. I suspect
you're hoping to use * as a wild-card, which means you must use Like, not =.
However, you don't really want to use Like when you do have a value, as your
query likely won't use indexes. As well, Like only works with text fields,
not numeric or date fields.

What's better is to leave the text box blank (which is really Null), and
then have your query have something like:

WHERE (Field1 = Forms!FormName!ControlName1 OR Forms!FormName!ControlName1
IS NULL)
AND (Field2 = Forms!FormName!ControlName2 OR Forms!FormName!ControlName2 IS
NULL)

and so on.

 

[Guest]

My prefered way of doing this is to create a stored query with no filtering
and build the Where clause dynamically based on whether the controls have
values in them.
The stored query is used only as a template. The query I use to do the work
I create dynamically. Here is an example:

strWhere = ""
If Not IsNull(Me.FirstControl) Then
strWhere = "[Field1] = '" & Me.FirstControl & "'"
End If
If Not IsNull(Me.SecondControl) Then
if Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = "[Field2] = " & Me.SecondControl
End If
If Not IsNull(Me.DateControl) Then
if Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = "[Field3] = #" & Me.DateControl & "#"
End If

Set qdfTmp = qdfs("zMyQuery")
Set qdf = qdfs("MyQuery")
'Get the template SQL
strSQL = qdfTmp.Sql
strSQL = Replace(strSQL,";", strWhere & ";")
'Saves the SQL
qdf.Sql = strSQL
Set qdf = Nothing
Set qdfTmp = Nothing

'Now the query is ready with the appropriate Where clause

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), are several small Access
database samples called "CreateQueries2.mdb", "CreateQueries4.mdb", and
"CreateQueries5.mdb" which illustrate several variations programmatically
create queries that also address this point.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L