Microsoft Access Office VB VBA Help and Examples

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
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
'there was no item in the list for that paramater
GetParameter = ""
Exit Function
'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.


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.

The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.