Chapter 19: Advanced Access Form Techniques
705
If Not IsNull(txtContactID.Value) Then
where = where _
& (“ [ContactID]= “ & Me![txtContactID] & “ “)
End If
If Not IsNull(txtLastName.Value) Then
If Len(where) > 0 Then
where = where _
& (“ OR [LastName]LIKE ‘“ & Me![txtLastName] & “*’ “)
Else
where = where _
& (“ [LastName]LIKE ‘“ & Me![txtLastName] & “*’ “)
End If
End If
If Not IsNull(txtCity.Value) Then
If Len(where) > 0 Then
where = where _
& (“ OR [City] LIKE ‘“ & Me![txtCity] & “*’ “)
Else
where = where _
& (“ [City] LIKE ‘“ & Me![txtCity] & “*’ “)
End If
End If
If Not IsNull(txtZipCode.Value) Then
If Len(where) > 0 Then
where = where _
& (“ OR [ZipCode] LIKE ‘“ & Me![txtZipCode] & “*’ “)
Else
where = where _
& (“ [ZipCode] LIKE ‘“ & Me![txtZipCode] & “*’ “)
End If
End If
Set QD = db.CreateQueryDef(“MyQuery”, _
“SELECT * FROM Contacts WHERE “ & where & “;”)
DoCmd.OpenQuery “MyQuery”
DoCmd.Close acForm, Me.Name
End SubNotice that the SQL statement is built up with the contents of the text boxes on the form. Each text
box’s value is added only when the text box is not null. Also, the length of the query string is eval-
uated before adding to the SELECT clause. The OR clause is added only when the SELECT clause
already contains a value so that the resulting SQL string looks something like this:
SELECT * FROM Contacts
WHERE ContactID = 17 OR City LIKE ‘New*’;Using LIKE and an asterisk after the text added from the contents of the form’s text boxes means
that the SQL statement is quite inclusive. The previous example returns records where the
ContactID is 17 and where the City begins with New (New York, New Haven, New Brunswick,
and so on). Remove the asterisks and change the LIKE clause to the equal sign (=) to make the