Multiple Query Parameters from a single Prompt
You have a query and want users to be able to
type in multiple parameters without having to fill in loads of boxes. This
example shows you how to get multiple parameters for a query with only one
prompt box. It uses a little bit of code which you can just copy without having
to modify.
Create a new module in your database. Paste
the following code into the module. Save the module. You can call it anything
you like. But a good name would be Global Functions
Public Function GetParameter(ParamNum As Integer,
str As String) As String
'Get the Nth Parameter from the string Str. Each paramater must be seperated
by a space'
Dim Seperator As String
Seperator = "," 'Change this if you want to use a different seperator. I've
used space
On Error GoTo errhandler
Dim x As Integer
Dim y As Long
Dim StartWord As Long
Dim lenWord As Long
y = 0
For x = 1 To ParamNum - 1
y = InStr(y + 1, str, Seperator)
If x > 1 And y = 0 Then
GoTo noitem
End If
Next
If IsNull(y) Then y = 0
StartWord = y + 1
y = InStr(y + 1, str, Seperator)
If IsNull(y) Or y = 0 Then y = Len(str) + 1
lenWord = y - StartWord
GetParameter = Trim(Mid(str, StartWord, lenWord))
Exit Function
noitem:
'there was no item in the list for that paramater
GetParameter = ""
Exit Function
errhandler:
'Errored out so str must be invalid
MsgBox ("When using Getparameter please ensure strings are seperated by
commas and no spaces")
End Function |
The function you have just pasted works in
the following way.
If you call Getparameter(1,"John
Paul James Hawkins") it will return "John"
If you call Getparameter(3,"John
Paul James Hawkins") it will return "James"
Okay now go into the query you want to use it
in and for the field you want to query type the following in the criteria box.
GetParameter(1,[Prompt])
Now repeat this for the maximum number
of Parameters you are going to allow, each time incrementing the number, but
keeping the prompt exactly the same. This way it will only ask once as access
assumes the variable is the same as the prompt is the same. You can always put
too many as it will just return Null if there is no Sixth parameter or whatever.
So it will look like
Run the query. It should ask for one prompt.
Which you may type in "1 2 3" and it will return all records whose num
fields are either 1, 2 or 3.
| 