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_Afte rUpdate()
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.txtZi pList) Then 'Show all if blank.
varZips = Split(Me.txtZip List, " ")
'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*******@mart ecgroup.com> wrote in message
news:11******** **************@ i40g2000cwc.goo glegroups.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.
