And Or in a Parameter Query

How do you make use of AND or OR in a Parameter Query? Entering one value
works fine, but using and/or retrieves an empty string. Any ideas?

A parameter query uses and /or just like any other query as far as I
know. You can't use and / or in the parameter section because you can't
have more then one data type assigned to a field. That is the point
behind a parameter query: Acccess is unable to determine what type of
data is being stored/returned, Currency, Date, whatever, so you use the
parameter to force it to work with that data one consistent way.

However, just because it is a parameter query, does not mean you can not
use and/or in the criteria like you would any other query.

Phil




On 12/16/2009 4:14 PM, NEWER USER wrote:
How do you make use of AND or OR in a Parameter Query? Entering one value
works fine, but using and/or retrieves an empty string. Any ideas?

OR: enter each criteria on a separate criteria line
AND: enter all criteria on one criteria line

You will often have combinations, such as this:

Looking up data where the quantity is = 15 and = 25 and the CustomerName
is "JONES" or "SMITH". In this case, JONES goes on one line in the
CustomerName criteria field, SMITH on the one below. The = 15 AND = 25 goes
on EACH line of the Quantity criteria field.

"NEWER USER" wrote:

How do you make use of AND or OR in a Parameter Query? Entering one value
works fine, but using and/or retrieves an empty string. Any ideas?

When the parameter box comes up for you to enter the value, are you typing
your values and 'and/or' in the box i.e. say you wanted 'Fred' or 'Joe' are
you typing Fred or Joe in the parameter box?

If so, you need to put two parameters in your query criteria, i.e.
[Parameter 1] or [Parameter 2], then you will get two boxes, type Fred in the
first and Joe in the second and it will work.

Let us know if any of the answers help you.....



"NEWER USER" wrote:

How do you make use of AND or OR in a Parameter Query? Entering one value
works fine, but using and/or retrieves an empty string. Any ideas?

As you can see, there is a little bit of confusion on whether you meant
"I want to enter Joe or Bob as a response to the parameter and have it find
records with Joe or Bob." Or whether you meant " I want to enter Joe in one
parameter prompt and Bob in a second parameter prompt and have records
returned with Joe or Bob."

Parameter prompts contain ONE value. So if you enter "Joe Or Bob" in a
parameter, the search will be for that string and there is probably no record
with "Joe Or Bob" as a value in one field. Parameter prompts cannot contain
conjunctions (and/or) or comparison operators (=,,) or arithmetic
expressions. They can contain strings with those items but they are just
characters in the string and have no effect on what happens in the query.

The best solution in this case is probably to build the query string (or at
least the filter - where clause) using VBA and a form to get the value or values.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Emma Hope wrote:
When the parameter box comes up for you to enter the value, are you typing
your values and 'and/or' in the box i.e. say you wanted 'Fred' or 'Joe' are
you typing Fred or Joe in the parameter box?

If so, you need to put two parameters in your query criteria, i.e.
[Parameter 1] or [Parameter 2], then you will get two boxes, type Fred in the
first and Joe in the second and it will work.

Let us know if any of the answers help you.....



"NEWER USER" wrote:

How do you make use of AND or OR in a Parameter Query? Entering one value
works fine, but using and/or retrieves an empty string. Any ideas?