Parameter Query - Multiple Terms- 1 field - How?

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.

-----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?
.

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?
.

.

On Wed, 18 Aug 2004 11:36:22 -0700, "LizW"
wrote:

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?

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 W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

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 Vinson wrote:

On Wed, 18 Aug 2004 11:36:22 -0700, "LizW"
wrote:

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?

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 W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

On Thu, 19 Aug 2004 16:58:21 -0400, "John Spencer (MVP)"
wrote:

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.

Sorry... quite correct of course, John!

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

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)" wrote:
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 Vinson wrote:

On Wed, 18 Aug 2004 11:36:22 -0700, "LizW"
wrote:

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?

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 W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public