Parameter Query - Multiple Terms- 1 field - How?

  • Thread starter Thread starter LizW
  • Start date Start date
L

LizW

Hi there,

I have a parameter query with wildcards set up for a table
with a long description field. It works great if the user
just wants to locate the word "pleading" or someone's
name.

However, most of the time, users want to look up several
string in a record's description field, like: pleading,
murphy, 45800.

How can I construct a parameter query, or other kind of
query that will allow the user to type in multiple terms
in one parameter box?
 
One parameter box? Can't. Sorry.

Proper database design would have you split the
description field in to three fields; FirstName, LastName
and SomeNumber. Then the quickest and easiest way (Not the
best) is the put an input box in the criteria section
under each field in the query. eg Under the FirstName
field "Like([Please Enter the First Name or *])" (sans
quotes)

Training also always helps with this sort of thing.
 
unfortunately this database was set up years ago and has
upwards of 12,000 entries!

The description field reads like text and could not have
been set up that way anyway. It reads like: "Case 23456
John Murphy, pleading, boxes 345-387, verdict in the case
overturned..."

Text is always variable, in paragraphs.

If parameter query doesn't work, can you suggest another
solution?


-----Original Message-----
One parameter box? Can't. Sorry.

Proper database design would have you split the
description field in to three fields; FirstName, LastName
and SomeNumber. Then the quickest and easiest way (Not the
best) is the put an input box in the criteria section
under each field in the query. eg Under the FirstName
field "Like([Please Enter the First Name or *])" (sans
quotes)

Training also always helps with this sort of thing.
-----Original Message-----
Hi there,

I have a parameter query with wildcards set up for a table
with a long description field. It works great if the user
just wants to locate the word "pleading" or someone's
name.

However, most of the time, users want to look up several
string in a record's description field, like: pleading,
murphy, 45800.

How can I construct a parameter query, or other kind of
query that will allow the user to type in multiple terms
in one parameter box?
.
Click to expand...
.
Click to expand...
 
How can I construct a parameter query, or other kind of
query that will allow the user to type in multiple terms
in one parameter box?
Click to expand...

As Cheval says, you can't. What you *can* do is arbitrarily limit the
user to (say) ten terms, in ten form textboxes; and use a criterion
like

[memofield] LIKE "*" & [Forms]![yourform]![term1] & "*"
OR
[memofield] LIKE "*" & [Forms]![yourform]![term2] & "*"
OR
[memofield] LIKE "*" & [Forms]![yourform]![term3] & "*"
OR
[memofield] LIKE "*" & [Forms]![yourform]![term4] & "*"
OR
<etc>

If the user leaves a term blank it will still work (returning all
records in the database if they leave all ten blank).
 
John,

Wouldn't leaving ANY one of the items blank return ALL records if you are using
OR? If you use AND then I think your solution would work to find records with
all designated strings.

MemoField Like ...
AND
MemoField Like ...

John said:
How can I construct a parameter query, or other kind of
query that will allow the user to type in multiple terms
in one parameter box?
Click to expand...

As Cheval says, you can't. What you *can* do is arbitrarily limit the
user to (say) ten terms, in ten form textboxes; and use a criterion
like

[memofield] LIKE "*" & [Forms]![yourform]![term1] & "*"
OR
[memofield] LIKE "*" & [Forms]![yourform]![term2] & "*"
OR
[memofield] LIKE "*" & [Forms]![yourform]![term3] & "*"
OR
[memofield] LIKE "*" & [Forms]![yourform]![term4] & "*"
OR
<etc>

If the user leaves a term blank it will still work (returning all
records in the database if they leave all ten blank).
Click to expand...
 
Wouldn't leaving ANY one of the items blank return ALL records if you are using
OR? If you use AND then I think your solution would work to find records with
all designated strings.
Click to expand...

Sorry... quite correct of course, John!
 
Hi Liz,

Not meaning to complicate things, but...

In situations like this in the past, I have used
a "Google model." I place 2 command buttons
on the form..

1) "I Feel Lucky"
- this would use the WHERE clause Mr. Spencer suggests.

2) "Match On Any"
- in VBA you would construct an "OR" WHERE clause
using *only* the search values that are "not blank."

On occasion, I have added a text box that DCount's number
of records that would be returned by the stored "AND" query
given the current entries in your search boxes. This can be an
easy way to give your users immediate feedback.

The point being that your user might not remember how to
spell "Murphy", or it was mistakenly entered as "Murphey"
in the memo field, etc.

The user types in "pleading" in first search box and tabs to
the second search box. The DCount textbox gets updated
to show say "325."

The user types in "Murphy" and tabs to the third search
box. The DCount textbox gets updated to zero. What's
going on?

The user then types in "45800", the DCount textbox will
still say zero, but can click on the "Match On Any" to scan
through results to see there is a record with "Murphey."

Again, I don't mean to complicate things, but every time
I read this thread, I kept feeling it should at least be suggested.

Good luck,

Gary Walter





John Spencer (MVP) said:
John,

Wouldn't leaving ANY one of the items blank return ALL records if you are using
OR? If you use AND then I think your solution would work to find records with
all designated strings.

MemoField Like ...
AND
MemoField Like ...

John said:
How can I construct a parameter query, or other kind of
query that will allow the user to type in multiple terms
in one parameter box?
Click to expand...

As Cheval says, you can't. What you *can* do is arbitrarily limit the
user to (say) ten terms, in ten form textboxes; and use a criterion
like

[memofield] LIKE "*" & [Forms]![yourform]![term1] & "*"
OR
[memofield] LIKE "*" & [Forms]![yourform]![term2] & "*"
OR
[memofield] LIKE "*" & [Forms]![yourform]![term3] & "*"
OR
[memofield] LIKE "*" & [Forms]![yourform]![term4] & "*"
OR
<etc>

If the user leaves a term blank it will still work (returning all
records in the database if they leave all ten blank).
Click to expand...
Click to expand...
 
Back
Top