Can a single query parameter allow multiple entries? (1 Viewer)

gojets1721

Registered User.
Local time
Today, 09:46
Joined
Jun 11, 2019
Messages
429
For instance, if I have a query parameter set up for "Employee Name:", is it possible to build it in a way that lets me input "Smith, Johnson" and it pulls up all entries will Smith or Johnson listed?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:46
Joined
Feb 28, 2001
Messages
26,999
Yes, but if and only if the SQL expects a list. Further, the syntax required might be a problem since Access default behavior on quoted strings is to strip one layer of quotes at each layer that passes a string.

This next is "air code" for illustrative purposes only.

Code:
SELECT EmpID, EmpLName, EmpFName, EmpDept FROM EmpTable WHERE EmpLName IN (parameter) ;

The parameter might have to be something like " 'Smith','Jones' " in order to be useful.
 

gojets1721

Registered User.
Local time
Today, 09:46
Joined
Jun 11, 2019
Messages
429
I added in his Solution 2 code and I am getting a syntax error....Can't really figure out why
 

June7

AWF VIP
Local time
Today, 08:46
Joined
Mar 9, 2014
Messages
5,423
Neither can we if you don't provide your attempt for analysis.

@The_Doc_Man, as theDBGuy explains in blog, array list for IN() cannot be dynamic in query parameter.

I never use dynamic parameterized queries. I prefer VBA to build filter criteria and apply to form or report when opening or apply to Filter property for already open form. Use a multi-select listbox for users to select multiple parameters. http://allenbrowne.com/ser-50.html
 

gojets1721

Registered User.
Local time
Today, 09:46
Joined
Jun 11, 2019
Messages
429
What do you mean? I followed theDBGuy's article exactly and used the Solution #2 code, but I got a syntax error. Plus, I am not looking to use a list box. There are thousands of employees. Being able to simply type in multiple names separated by a semicolon would be much easier.

Here's the code I used:
Code:
WHERE ";" & [Enter Employee Name (separated by a semicolon ';')] & ";" Like "*;" & EmployeeName & ";*"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:46
Joined
Oct 29, 2018
Messages
21,358
What do you mean? I followed theDBGuy's article exactly and used the Solution #2 code, but I got a syntax error. Plus, I am not looking to use a list box. There are thousands of employees. Being able to simply type in multiple names separated by a semicolon would be much easier.

Here's the code I used:
Code:
WHERE ";" & [Enter Employee Name (separated by a semicolon ';')] & ";" Like "*;" & EmployeeName & ";*"
Hi. Thank you for reading the article. You may be getting an error because your situation is a little different than the article's. It looks like your criteria is for a Text field rather than a Numeric field. If so, could you please try entering the following format at the prompt?
Code:
'Joe';'John';'Mary'
Just checking... (untested)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:46
Joined
Oct 29, 2018
Messages
21,358
Hi. Thank you for reading the article. You may be getting an error because your situation is a little different than the article's. It looks like your criteria is for a Text field rather than a Numeric field. If so, could you please try entering the following format at the prompt?
Code:
'Joe';'John';'Mary'
Just checking... (untested)
Well, I just tested it, and I didn't need to enter the single quotes between the names. Can you please post the complete SQL statement for your query? Thanks.
 

gojets1721

Registered User.
Local time
Today, 09:46
Joined
Jun 11, 2019
Messages
429
For simplicity sake, we can keep it as numerical, as I would like to be able to search the ID as well. Plus that is what the blog post is focused on. I again repeated the steps but am still getting the syntax error.

Here is my SQL code without the parameter code:

SELECT [2019].[EventID], [2019].[SubmissionDate], [2019].[SubmittedBy], [2019].[EmployeeResponsible], [2019].[CustomerAccountID], [2019].[Customer First Name], [2019].[CustomerLastName], [2019].[Store], [2019].[DateofEvent], [2019].Category, [2019].Description, [2019].[Follow Up]
FROM 2019
ORDER BY [2019].[Event #] DESC;
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:46
Joined
Oct 29, 2018
Messages
21,358
For simplicity sake, we can keep it as numerical, as I would like to be able to search the ID as well. Plus that is what the blog post is focused on. I again repeated the steps but am still getting the syntax error.

Here is my SQL code without the parameter code:

SELECT [2019].[EventID], [2019].[SubmissionDate], [2019].[SubmittedBy], [2019].[EmployeeResponsible], [2019].[CustomerAccountID], [2019].[Customer First Name], [2019].[CustomerLastName], [2019].[Store], [2019].[DateofEvent], [2019].Category, [2019].Description, [2019].[Follow Up]
FROM 2019
ORDER BY [2019].[Event #] DESC;
Hi. Unfortunately, it was important to see the parameter part of the SQL statement in case the error is within it. Are you saying the syntax error is in this part of the SQL statement? If not, can you please post the "complete" SQL statement, including the parameter part, because it is that part that you're asking for help anyway, correct? If I were to give it a try, the SQL statement might look something like this:
Code:
SELECT EventID, SubmissionDate, SubmittedBy, EmployeeResponsible,
   CustomerAccountID, [Customer First Name], CustomerLastName,
   Store, DateofEvent, Category, Description, [Follow Up]
FROM [2019]
 WHERE ";" & [Enter EventIDs] & ";" Like "*;" & [EventID] & ";*"
ORDER BY [Event #]
 

gojets1721

Registered User.
Local time
Today, 09:46
Joined
Jun 11, 2019
Messages
429
That actually worked! I was being really dumb and putting the Where code in design view, and not SQL. Thank you!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:46
Joined
Oct 29, 2018
Messages
21,358
That actually worked! I was being really dumb and putting the Where code in design view, and not SQL. Thank you!
Oh, yes, that would definitely create a "syntax" error. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom