Search by two fields?

[Melissa]

I'd very much appreciate any help I could receive- this
may be a silly question but I can't figure it out for the
life of me.

In MS Access, I've designed a table to store quotes, with
fields for Author, Quote, Source, Date, etc. I also have
two fields, that I made into combo boxes, for keywords
that give me a general theme of the quote. The combo
boxes draw from another table (a list of the keywords).
One record might use the keywords "Happiness"
and "Money". The next might use "Money" and "Politics" or
whatever.

My problem is this- say I want to look up all quotes that
have to do with "Politics" and "Happiness". Either of
these words might appear in Keyword Field 1 or Keyword
Field 2. How do I search my database for something that
might appear in either field? I've been experimenting
with queries, filters, criteria, etc. and none of it is
making sense to me anymore. Can anyone help? Please?

 

[John Vinson]

How do I search my database for something that
might appear in either field?

With OR logic. Create a Query; under the first keyword field put

[Enter keyword:]

and then put exactly the same prompt under the other keyword field, ON
THE NEXT LINE so that it will find records where the first keyword is
what the user enters, *or* the second matches.

 

[Van T. Dinh]

Use the criteria:

....
WHERE (Keyword1 = "Politics") OR (Keyword1 = "Happiness")
OR (Keyword2 = "Politics") OR (Keyword2 = "Happiness")

You may need to look at re-designing the Table Structure
since with present structure, you a limited to 2 Keywords.
What if you want 3 or 4 Keywords per quote? As many
Keywords as needed?

HTH
Van T. Dinh
MVP (Access)

 

[Pete Sheridan]

Hello Melissa,

Goto the Microsoft Knowledge base and use QBF as your
search argument. You will get a number of hits on 'QBF
(Query by form)'.

I have implemented 'QBF' for a number of forms and
reports. It allows the maximum flexibility in the
selection of the criteria to be used to select records
into a dataset.

The QBF (selection)form should be an unbound form (no
underlying query to populate it). Each field in the form
should be named in such a way as to reflect the field in
the table that it will select data from; ie. [txtLName]
for use in selecting the 'LName' field in the query,
[txtCity] for use in selecting the 'City' field in the
query, etc... (this makes it easier to work with)

You should also have a command button to start the process
after all of the criteria is entered. It can start
a 'Macro' or some 'VBA' code to accomplish the task at
hand.

Now the fun! How does the query that drives a form or
report use the selection criteria that you have keyed into
the selection form? If a field in the selection form is
left blank, how is it handled? By using the following
string in the criteria line fot the field in the query:

Like [forms]![frmQBF_AddrBookSearch]![txtLName] & "*"

where '[forms]![frmQBF_AddrBookSearch]![txtLName]' points
to any value typed into the [txtLName] field in the
selection form. The query looks at the form text box entry
and inserts it into the criteria line for the field. It
also allows the wildcard '*' character to be used if no
selection criteria is entered.

I hope this helps you,
Pete Sheridan
email: (e-mail address removed)

PS: I purposely didn't put my email in the 'sender's e-
mail' text box because of the amount of spam I started to
receive after I answered the first few questions.