MS Access Multiple values in parameters

Question

So I am trying to figure out what I am doing wrong, I have a table I am trying to run a query which asks the user if they have a specific district they are looking for....

so my columns are

name    district   etc
Jay       1
Tom       3
Mary      5
Tim       5
Mike      15

I am trying to be able to have a parameter [which district?:] 1,5 .. It would display

Jay      1
Mary     5
Tim      5

Code:

WHERE 
    [District] Like "*" & [What District(s)] 
    & "*" OR [What District(s)] Like "*" & [District] & "*"

but I am getting

Jay       1
Mary      5
Tim       5
Mike      15

I am trying to avoid getting the "15" record.

Won’t return any response when I put a , into the parameter. — Jay
– Jay, Commented Sep 13, 2019 at 22:09

Parfait · Accepted Answer · 2019-09-13 21:26:23Z

1

Consider reversing the LIKE expressions and checking by comma positions for 1) comma after (#,) 2) comma before (,#), or 3) before and after (,#,) and then exact or no comma (=).

SELECT *
FROM Employees
WHERE 
       [What District(s)] LIKE [District] & ',*'
    OR [What District(s)] LIKE '*,' & [District]  & ',*'
    OR [What District(s)] LIKE '*,' & [District] 
    OR [Employee ID] = [What District(s)]

Advise user not to include whitespaces between entries as a complexity error strangely raises.

edited Sep 13, 2019 at 21:26

answered Sep 13, 2019 at 21:01

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jay Over a year ago

This worked for me ! I’m testing it to make sure no issues but looks good to go. Thanks

GMB · Accepted Answer · 2019-09-13 20:51:35Z

1

If you are dealing with a list of numbers separated by a quote, you can use the IN operator, like:

""WHERE [District] IN ("" & [What District(s)] & "")""

This would generate a valid SQL expression like:

WHERE [District] IN (1,5)

Reference : the IN operator in ms-access

edited Sep 13, 2019 at 20:51

answered Sep 13, 2019 at 20:39

GMB

224k25 gold badges102 silver badges151 bronze badges

3 Comments

Parfait Over a year ago

This will not work if [What District(s)] is a parameter instead of concatenated variable.

GMB Over a year ago

@Parfait: probably not indeed. But since the OP is showing concatenated variables in the first place, this still looks like an acceptable solution...

Parfait Over a year ago

Parameters can be concatenated inside string literals.

Collectives™ on Stack Overflow

MS Access Multiple values in parameters

2 Answers 2

1 Comment

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Related