Can I enter multiple zip codes in a parameter query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to search my db using a parameter query that will accept one or more
zip codes. A combo list is too cumbersome and I know the zip codes before
hand so I just need to input and search.

Thanks.
 
Query parameters are not powerful enough to do that.

Instead use a text box on a form where the user can enter their list, and
use code to build the Filter for the form (or the WhereCondition for
OpenReport of whatever result you need to see.)

The code will use Split() to parse the items from the text box, and build a
WHERE string from the results.

This example applies to muliple keywords (or partial keywords) entered into
a text box, and matched to a field named Notes. Adapt it to your needs:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub
 
Allen,

All the code is in but when I enter two zip codes (with a space between
them) the entire db and all its fields appear. I have a query that's named
'qryDealer' and the text box is called 'txtZipCode'. I want the text box to
use the query and return only the zip codes and the other fields in the query
that I entered.

Any suggestions are greatly appreciated.

Allen Browne said:
Query parameters are not powerful enough to do that.

Instead use a text box on a form where the user can enter their list, and
use code to build the Filter for the form (or the WhereCondition for
OpenReport of whatever result you need to see.)

The code will use Split() to parse the items from the text box, and build a
WHERE string from the results.

This example applies to muliple keywords (or partial keywords) entered into
a text box, and matched to a field named Notes. Adapt it to your needs:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

dapetrella said:
I want to search my db using a parameter query that will accept one or more
zip codes. A combo list is too cumbersome and I know the zip codes before
hand so I just need to input and search.

Thanks.
Click to expand...
Click to expand...
 
A query cannot do that (unless you re-write the SQL property of the QueryDef
each time.)

Instead, use a Continuous Form where there results are shown in the Detail
section, one per row. In the Form Header section, you place the text box for
entering the values, and the command button to filter it. In the Click event
of the button, you run the code that builds the string that filters the
form. In the end, the form shows only matches, one per row.

There's a screenshot of that kind of thing in this article:
Search criteria
at:
http://allenbrowne.com/ser-62.html
The sample database in that article does more than you need, but it should
give you the picture.

If you do want to modify the query anyway, you need to build the entire SQL
string (not just the WHERE clause), and then:
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

dapetrella said:
Allen,

All the code is in but when I enter two zip codes (with a space between
them) the entire db and all its fields appear. I have a query that's
named
'qryDealer' and the text box is called 'txtZipCode'. I want the text box
to
use the query and return only the zip codes and the other fields in the
query
that I entered.

Any suggestions are greatly appreciated.

Allen Browne said:
Query parameters are not powerful enough to do that.

Instead use a text box on a form where the user can enter their list, and
use code to build the Filter for the form (or the WhereCondition for
OpenReport of whatever result you need to see.)

The code will use Split() to parse the items from the text box, and build
a
WHERE string from the results.

This example applies to muliple keywords (or partial keywords) entered
into
a text box, and matched to a field named Notes. Adapt it to your needs:

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

dapetrella said:
I want to search my db using a parameter query that will accept one or
more
zip codes. A combo list is too cumbersome and I know the zip codes
before
hand so I just need to input and search.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top