Parameter query syntax for multiples (1 Viewer)

BJF

Registered User.
Local time
Today, 04:03
Joined
Feb 19, 2010
Messages
133
Hi,

I wasnt quite able to find an answer anywhere yet for what i am looking to do, so here it goes...

I have a parameter query set up to run a report which is simply asking for an employee id.

So in the query I have [Enter Employee ID] in the criteria section for the EmployeeID field.

When i run it it works fine if i put in a single number like 11, and i get Employee 11's results in my report.

However i cant figure how to put in more than one employee.

Example; if i put the following straight into the criteria for EmployeeID in the query, i get results from these four employees which is great.

11 Or 13 Or 14 Or 27

However if i leave it like i originally had it as a parameter [Enter Employee ID] and put in 11 Or 13 Or 14 Or 27 to the parameter, i get no results.

I tried quotes, semicolons, commas - i just cant get it to work with multiple criteria.

Can anyone help me?
Thank you,
Brian
 

June7

AWF VIP
Local time
Today, 00:03
Joined
Mar 9, 2014
Messages
5,423
I don't use dynamic parameterized queries, especially not with popup input prompts as cannot validate input. What you want won't work with one unless you want to limit to 4 employees. In which case you need 4 inputs.
[Enter Employee ID 1] Or [Enter Employee ID 2] Or [Enter Employee ID 3] Or [Enter Employee ID 4]

Review: http://allenbrowne.com/ser-62.html

You will likely need to use a listbox to select multiple employees then code loops through the listbox to build filter criteria. The tutorial has a link for example of this.
 
Last edited:

plog

Banishment Pending
Local time
Today, 03:03
Joined
May 11, 2011
Messages
11,611
You can't do that simply via the query. The best way to accomplish what you want is to build a form which allows users to input multiple ids, click a button and then VBA uses the DoCmd.OpenReport (https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openreport) to open the report while passing it a filter comprised of all the ids they entered.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:03
Joined
Oct 29, 2018
Messages
21,357
Hi,

As was already mentioned, it is hard to use a parameter prompt because you can't really validate the input. For example, if you were able to enter 1 or 2 or 3 in the prompt and it worked, you'll have to make sure users don't enter something like 1,2,3 or 1/2/3, or how about 1 and 2 and 3?

Still, if you're able to train the user to enter a consistent format, then it's possible to use a parameter prompt for multiple criteria. So, it really comes down to expecting a specific format and then handling it rather than having something that will simply accept anything the user enters.

Just my 2 cents...
 

plog

Banishment Pending
Local time
Today, 03:03
Joined
May 11, 2011
Messages
11,611
then it's possible to use a parameter prompt for multiple criteria

No it's not. A parameter doesn't look for logic, it sucks in whatever the user types as one single string value, it doesn't parse anything. Thus, this:

WHERE (YourField = [InputParameter])

becomes this:

WHERE (YourField = "1 OR 2 OR 3 OR 4")

They system turns whatever is input into a string and inserts it wholesale into the query with quote marks around it. It sees the ORs, but thinks its part of the input value. This is the case even if you use IN instead of an equals comparison:

WHERE (YourField IN ("1 OR 2 OR 3 OR 4"))
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:03
Joined
Oct 29, 2018
Messages
21,357
No it's not. A parameter doesn't look for logic, it sucks in whatever the user types as one single string value, it doesn't parse anything.

Hi,

I agree with what you’re saying 100% except I wasn’t lying either. I can create a parameter query where the user can enter multiple values as a criteria. However, I also prefer using a form instead.

Just my 2 cents...

Sent from phone...
 

plog

Banishment Pending
Local time
Today, 03:03
Joined
May 11, 2011
Messages
11,611
How? Can you provide the SQL?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:03
Joined
Oct 29, 2018
Messages
21,357
How? Can you provide the SQL?

Hi,

I’ll be happy to, as soon as I get in front of a computer. It’s basically not what you were thinking.

Cheers!

Sent from phone...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:03
Joined
Oct 29, 2018
Messages
21,357
Hi,

I just to let everyone know I haven't forgotten this thread. I thought it would be simpler to post a link to a previous discussion showing what I meant but I couldn't find a quick example. So, I have decided, instead, to write an article about it. I'll be back to post a link in a couple of days.

Thank you.

DBG
 

plog

Banishment Pending
Local time
Today, 03:03
Joined
May 11, 2011
Messages
11,611
Huh? All we need is some SQL.

MyTable
MyField
1
2
3
4

Now we just need your SQL such that when I double click the query, a parameter input pop-ups up, I type in "1 OR 3 OR 7", hit OK and get the appropriate 2 records returned.
 

Users who are viewing this thread

Top Bottom