Parameter Query

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

Guest

I am running into the following issue and was wondering if anyone could help.
I have a parameter query that prompts the user to input a city code (ie.
PHX). The issue is that sometimes I need multiple codes to be inputted, but
when I try putting in (PHX or SAN) it doesn't work. I have even tried using
'PHX' or 'SAN'.

Is it possible to do this
 
Yes. But the way you are trying to do this will only accept one parameter at
the most. Here's two good examples from MS

Create a public module and copy and paste these two functions

Function GetToken(stLn, stDelim)
Dim iDelim As Integer, stToken As String
'Searches for commas as a delimiter
iDelim = InStr(1, stLn, stDelim)
If (iDelim <> 0) Then
stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
stLn = Mid$(stLn, iDelim + 1)
Else
stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
stLn = ""
End If
GetToken = stToken
End Function

Function InParam(Fld, Param)


Dim stToken As String
'The following two lines are optional, making queries
'case-insensitive
Fld = UCase(Fld)
Param = UCase(Param)
If IsNull(Fld) Then Fld = ""

'Parses out Values separated by commas
Do While (Len(Param) > 0)
stToken = GetToken(Param, ",")
If stToken = LTrim$(RTrim$(Fld)) Then

InParam = -1
Exit Function
Else
InParam = 0
End If
Loop
End Function


Then copy and paste this SQL code into a SQL query, then check it in design
view. Make sure to select your fields (this is from a test db)

SELECT Customers.CompanyName, Customers.CityCode, InParam([CityCode],[Type a
list like "CA, NY, NJ"])
FROM Customers
WHERE ((( InParam([CityCode],[Type a list like "CA, NY, NJ"]))=True));

Make sure your last column is set to 'Do not show' (so uncheck it)
then you can enter multiple city codes and they'll be returned by your query
 
Is there an easier way to do this? To have to type all city codes into a list
would take forever. There is not another way to have a parameter query
receive two parameter criteria into the same field?

Leo said:
Yes. But the way you are trying to do this will only accept one parameter at
the most. Here's two good examples from MS

Create a public module and copy and paste these two functions

Function GetToken(stLn, stDelim)
Dim iDelim As Integer, stToken As String
'Searches for commas as a delimiter
iDelim = InStr(1, stLn, stDelim)
If (iDelim <> 0) Then
stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
stLn = Mid$(stLn, iDelim + 1)
Else
stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
stLn = ""
End If
GetToken = stToken
End Function

Function InParam(Fld, Param)


Dim stToken As String
'The following two lines are optional, making queries
'case-insensitive
Fld = UCase(Fld)
Param = UCase(Param)
If IsNull(Fld) Then Fld = ""

'Parses out Values separated by commas
Do While (Len(Param) > 0)
stToken = GetToken(Param, ",")
If stToken = LTrim$(RTrim$(Fld)) Then

InParam = -1
Exit Function
Else
InParam = 0
End If
Loop
End Function


Then copy and paste this SQL code into a SQL query, then check it in design
view. Make sure to select your fields (this is from a test db)

SELECT Customers.CompanyName, Customers.CityCode, InParam([CityCode],[Type a
list like "CA, NY, NJ"])
FROM Customers
WHERE ((( InParam([CityCode],[Type a list like "CA, NY, NJ"]))=True));

Make sure your last column is set to 'Do not show' (so uncheck it)
then you can enter multiple city codes and they'll be returned by your query




James C. said:
I am running into the following issue and was wondering if anyone could help.
I have a parameter query that prompts the user to input a city code (ie.
PHX). The issue is that sometimes I need multiple codes to be inputted, but
when I try putting in (PHX or SAN) it doesn't work. I have even tried using
'PHX' or 'SAN'.

Is it possible to do this
Click to expand...
Click to expand...
 
in one of the columns of your query put this: Expr1: InStr([Type Member
Numbers separated by commas,Blank=All],[MemberNumber]) where Member Number
is your field (City code?) also, in the criteria put >0 .

The only drawback is that it may pull more records than you want due to the
InStr, but will bring back at least the records you do want. I had the same
problem you had and this worked.

James C. said:
Is there an easier way to do this? To have to type all city codes into a list
would take forever. There is not another way to have a parameter query
receive two parameter criteria into the same field?

Leo said:
Yes. But the way you are trying to do this will only accept one parameter at
the most. Here's two good examples from MS

Create a public module and copy and paste these two functions

Function GetToken(stLn, stDelim)
Dim iDelim As Integer, stToken As String
'Searches for commas as a delimiter
iDelim = InStr(1, stLn, stDelim)
If (iDelim <> 0) Then
stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
stLn = Mid$(stLn, iDelim + 1)
Else
stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
stLn = ""
End If
GetToken = stToken
End Function

Function InParam(Fld, Param)


Dim stToken As String
'The following two lines are optional, making queries
'case-insensitive
Fld = UCase(Fld)
Param = UCase(Param)
If IsNull(Fld) Then Fld = ""

'Parses out Values separated by commas
Do While (Len(Param) > 0)
stToken = GetToken(Param, ",")
If stToken = LTrim$(RTrim$(Fld)) Then

InParam = -1
Exit Function
Else
InParam = 0
End If
Loop
End Function


Then copy and paste this SQL code into a SQL query, then check it in design
view. Make sure to select your fields (this is from a test db)

SELECT Customers.CompanyName, Customers.CityCode, InParam([CityCode],[Type a
list like "CA, NY, NJ"])
FROM Customers
WHERE ((( InParam([CityCode],[Type a list like "CA, NY, NJ"]))=True));

Make sure your last column is set to 'Do not show' (so uncheck it)
then you can enter multiple city codes and they'll be returned by your query




James C. said:
I am running into the following issue and was wondering if anyone could help.
I have a parameter query that prompts the user to input a city code (ie.
PHX). The issue is that sometimes I need multiple codes to be inputted, but
when I try putting in (PHX or SAN) it doesn't work. I have even tried using
'PHX' or 'SAN'.

Is it possible to do this
Click to expand...
Click to expand...
Click to expand...
 
Back
Top