By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 2,154 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

List query

P: n/a
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.

May 22 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.

May 22 '06 #2

P: n/a
fo*******@martecgroup.com wrote in
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.

To have the query prompt:
SELECT * from ZipCodes WHERE zipcode IN ([Enter zipcodes with comma
between])

To get the list from a textbox:
SELECT * from ZipCodes WHERE zipcode IN ([Forms!myform!txtZiplist])
--
Bob Quintal

PA is y I've altered my email address.
May 22 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.