Parameter Query

  • Thread starter Thread starter Janet F
  • Start date Start date
J

Janet F

Hello,

I have a parameter query which brings up a message box
asking for a key word on which my report is based. On
the criteria line of the query, in the key word field, I
used:
Like "*" & [Enter the key word to search by:] & "*"

This works, but I want to be able to ask for a second and
a third key word. In other words, have two "Or's." When
I put the phrase I used on the criteria line into the Or
lines of the key word field, Access ignores the fact that
I've done that and still only asks for one key word. Any
ideas? Thanks.

Janet
 
Hello,

I have a parameter query which brings up a message box
asking for a key word on which my report is based. On
the criteria line of the query, in the key word field, I
used:
Like "*" & [Enter the key word to search by:] & "*"

This works, but I want to be able to ask for a second and
a third key word. In other words, have two "Or's." When
I put the phrase I used on the criteria line into the Or
lines of the key word field, Access ignores the fact that
I've done that and still only asks for one key word. Any
ideas? Thanks.

Janet
Click to expand...

You have to change the second (and third, if you use 3) parameter
prompts, otherwise Access will use the first prompt in the OR clause
as well.
Any change (such as just dropping the final ":") would work. So will
the below prompts.

Like "*" & [Enter the first key word to search by:] & "*"
Like "*" & [Enter the second key word to search by:] & "*"
Like "*" & [Enter the third key word to search by:] & "*"
 
If you have the same exact phrase, Access treats it as the same parameter. So
once you have set one, you've set all.

Before we can fully answer the question, what do you want to have happen if you
fill in the first parameter and ignore (cancel) the 2nd and 3rd parameters.

One way to handle this, but that requires you to have three different criteria
and fill them in each time is to use criteria like:

Like "*" & [Enter First Key Word] & "*"
Like "*" & NZ([Enter Second Key Word],[Enter First Key Word] & "*"
Like "*" & NZ([Enter Third Key Word],[Enter First Key Word] & "*"

This will return records matching your key word or key words. If you Cancel the
second or third parameter prompt they will default to the First key word. If
you leave all three blanks you should get all records. Be aware that you MUST
enter the parameter phrase [Enter First Key Word] exactly the same all three
times. An extra space or a slight difference in the spelling and Access will
treat this as another parameter.

There are more complex ways to handle this that work better, but they involve
forms, comboboxes or listboxes, and usually VBA code. The more complex method
makes life simpler for the user but involves more work for the programmer.
 
Back
Top