Multiple query fields

[roostor]

Hello, I have a need for my users to search for a drug name either by
Brand Name or Generic Name in a database I am building. Both are
fields in my table and users can search by one or the other but not
both. I have a query that pulls search criteria from an unbound form
where users can enter any or all of the name of the drug they are
looking for. The problem is that the query is built using one query
with the following criteria:

Like (Forms]![frmSelect]![Text2] & "*") for brand name searches
Like (Forms]![frmSelect]![Text0] & "*") for generic name searches

The search works great with only one field, but adding both mucks
things up. How do I make them independent of one another? I thought
about 2 queries with command buttons that run their respective
queries, but is there a way to do it in 1 query?

Any help is appreciated. Thanks.

 

[Clifford Bass]

Hi,

How about just using one drug name field on the form and automatically
searching both drug name fields:

select *
from tblDrugs
where Brand_Name like [Forms]]![frmSelect]![Text0] & "*" or Generic_Name
like [Forms]]![frmSelect]![Text0] & "*"

Clifford Bass