This example assumes the person enters the zip codes into a text box,
separating them with spaces. You set the AfterUpate property of the text
box to [Event Procedure]. In this event, you save any edits that are in
process, parse the zip codes into an array, and build that into a string to
use as the Filter of the form. The field to be filtered is a Text type
field, named Zip. If the user entered no zip codes, you show all records.
Private Sub txtZipList_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varZips As Variant 'Array of zip codes
Dim i As Integer
Dim lngLen As Long
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If Not IsNull(Me.txtZipList) Then 'Show all if blank.
varZips = Split(Me.txtZipList, " ")
'Build up the Where string from the array.
For i = LBound(varZips) To UBound(varZips)
strWord = Trim$(varZips(i))
If strWord <> vbNullString Then
strWhere = strWhere & """" & strWord & """, "
End If
Next
'Remove trailing comma and space, and use IN.
lngLen = Len(strWhere) - 2
If lngLen > 0 Then
strWhere = "[Zip] IN (" & Left(strWhere, lngLen) & ")"
End If
End If
'Apply the filter.
If strWhere <> vbNullString Then
Me.Filter = strWhere
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub
--
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.
<fo*******@martecgroup.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...
Need help setting a select query where person enters a list of zipcodes
to lookup. I want to prompt the user to enter all the zip codes, then
run the query against to table to pull all records. Thanks.
