Parameter Queries with OR

[LeLe]

For some reason my parameter query with the expression [enter state(s)] only
works when I enter one state. I would like be able to enter NH Or VT Or ME,
and I can do this "manually" and get the correct result; however, if I try to
enter the exact same text NH Or VT Or ME in the parameter box, the query does
not return any results. I have tried putting quotes around the state names
but it did not make a difference.

Any help is greatly appreciated.

 

[Allen Browne]

You cannot include the operator (OR) as well as the literal values (state
names) in a parameter.

You may be able to parse the text box in code, to create a string to filter
your form with.

 

[LeLe]

Could you give me an example? I want the query to ask me which states I want
to include.

You cannot include the operator (OR) as well as the literal values (state
names) in a parameter.

You may be able to parse the text box in code, to create a string to filter
your form with.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

For some reason my parameter query with the expression [enter state(s)]
only
works when I enter one state. I would like be able to enter NH Or VT Or
ME,
and I can do this "manually" and get the correct result; however, if I try
to
enter the exact same text NH Or VT Or ME in the parameter box, the query
does
not return any results. I have tried putting quotes around the state
names
but it did not make a difference.

Any help is greatly appreciated.

 

[Bob Quintal]

For some reason my parameter query with the expression [enter
state(s)] only works when I enter one state. I would like be
able to enter NH Or VT Or ME, and I can do this "manually" and get
the correct result; however, if I try to enter the exact same text
NH Or VT Or ME in the parameter box, the query does not return any
results. I have tried putting quotes around the state names but
it did not make a difference.

Any help is greatly appreciated.

You can't do that. The query's parameter parsing routines were not set
up to handle this complexity.

You can create 3 or 4 separate parameters, and add them to the query

 

[Douglas J. Steele]

The problem is that the SQL of your query is something like:

WHERE ((TableName.State) = [Parameter])

so it becomes

WHERE ((TableName.State) = "NH Or VT Or ME")

which obviously is incorrect. When you put NH Or VT Or ME as the parameter
in the grid, Access is smart enough to generate SQL along the lines of

WHERE ((TableName.State) = "NH" Or (TableName.State) ="VT" Or
(TableName.State) ="ME")

One approach (although it may not be that efficient) is to have the user
type in the names comma-separated, so that you'd be typing in NH,VT,ME

You'd then change the SQL of your query from

WHERE InStr("," & [Parameter] & ",", "," & TableName.State & ",") > 0

or, if your users might inadvertently put in spaces with the commas

WHERE InStr("," & Replace([Parameter], " ", "") & ",", "," & TableName.State
& ",") > 0

 

[Allen Browne]

Are you comfortable writing VBA code?

It might be easiest to offer a multi-select list box where the user can
select the items they want, and you build the filter string from that.
Here's an example of that approach:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

If you just let the user type the values into a text box, you would need to
parse the string. Did the user separate the items with spaces? Commas?
Semi-colons? Periods? Did they include an operator (such as OR)? Did they do
a combination of these things? Did they include brackets? Did they do a
combination of these things (dots and brackets and spaces...?) It really
gets quite messy trying to parse free-form text like that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Could you give me an example? I want the query to ask me which states I
want
to include.

You cannot include the operator (OR) as well as the literal values (state
names) in a parameter.

You may be able to parse the text box in code, to create a string to
filter
your form with.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

For some reason my parameter query with the expression [enter state(s)]
only
works when I enter one state. I would like be able to enter NH Or VT
Or
ME,
and I can do this "manually" and get the correct result; however, if I
try
to
enter the exact same text NH Or VT Or ME in the parameter box, the
query
does
not return any results. I have tried putting quotes around the state
names
but it did not make a difference.

Any help is greatly appreciated.