query several fields

[goodmant]

I have a data base table with the following fields:
Town
Last Name
Lot

I want to have one query whereby I can query for example 1 of 3 fields, or 2
of 3 fields or 3 of 3 fields.

For example I may want to query to find all records where the Town is:
Northtown.
I do not know the Last Name or Lot.

Next I want to query the Town field for Northtown and Lot 12. I want to see
all records that are Northtown 12. In this way I will find all the Last Names
for these records.

I may be adding several fields to my table and basically would like one
query to query which ever field or combination of fields for which I have
info and see the entire record(s).

When I run my query I get the input popup for each field but if I leave and
of the popups empty (because I don't or don't want this info) the results
comes up empty.

Any help is appreciated.
Thanks

 

[golfinray]

Use the Like command. In the first field in the criteria row, put Like
"northtown." In the second field put Like "lot21"

 

[Jeff Boyce]

If you don't include a wildcard when you use "Like", I believe Access only
does a "MATCHES" comparison.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John Spencer]

IF the three fields always have data then you can use criteria like

Field: Town
Criteria: LIKE NZ([What Town?],"*")

Field: [Last Name]
Criteria: Like Nz([What Name?],"*")

Field: Lot
Criteria: Like Nz([What Lot?],"*")

If you wanted to have the option to match the name by the start of the name,
you could change the criteria to

Field: [Last Name]
Criteria: Like [What Name?] & "*"

Then if you entered "SPEN" in response to the prompt, you would get records
with Spencer, Spenc, and Spenser in the last name field. And if you left it
blank, you would get all records that had some value in the last name field.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I have a data base table with the following fields:
Town
Last Name
Lot

I want to have one query whereby I can query for example 1 of 3 fields, or 2
of 3 fields or 3 of 3 fields.

For example I may want to query to find all records where the Town is:
Northtown.
I do not know the Last Name or Lot.

Next I want to query the Town field for Northtown and Lot 12. I want to see
all records that are Northtown 12. In this way I will find all the Last Names
for these records.

I may be adding several fields to my table and basically would like one
query to query which ever field or combination of fields for which I have
info and see the entire record(s).

When I run my query I get the input popup for each field but if I leave and
of the popups empty (because I don't or don't want this info) the results
comes up empty.

Any help is appreciated.
Thanks

It's with some hesitation that I'll disagree with my friends John and Jeff,
but the LIKE operator isn't the only way to do this... and it can have some
problems (e.g. if you're looking for last name "Johns" you'll find "Johnson").

An alternative is to have a Form named frmCrit with three textboxes -
txtFindTown, txtFindLast, txtFindLot. Your Query would then have a criterion
on Town of

=[Forms]![frmCrit]![txtFindTown] OR [Forms]![frmCrit]![txtFindTown] IS NULL

and similarly for the other two fields.

If you need more fields then this, the query can get awfully complicated,
especially if you go from query grid view to SQL view and back. There are
"query by form" methods that can actually build the SQL string of a query
dynamically, based on which form controls are filled in and which are null.

 

[John Spencer]

You can disagree with me if you wish. I did place the caveat that the three
fields would have to always have a value (no nulls) in every record.

And if you don't use a wildcard except as a tool to "ignore" the criteria (my
first option) you don't get unexpected matches. Like with no wildcards
operates just like equal. It even uses indexes.

I like your alternatives and I usually build the query string if it is getting
this complex since it does not take too many sets of criteria using a
construct like
=[Forms]![frmCrit]![txtFindTown] OR [Forms]![frmCrit]![txtFindTown] IS NULL
before the query gets too complex to execute or extremely difficult to edit.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I have a data base table with the following fields:
Town
Last Name
Lot

I want to have one query whereby I can query for example 1 of 3 fields, or 2
of 3 fields or 3 of 3 fields.

For example I may want to query to find all records where the Town is:
Northtown.
I do not know the Last Name or Lot.

Next I want to query the Town field for Northtown and Lot 12. I want to see
all records that are Northtown 12. In this way I will find all the Last Names
for these records.

I may be adding several fields to my table and basically would like one
query to query which ever field or combination of fields for which I have
info and see the entire record(s).

When I run my query I get the input popup for each field but if I leave and
of the popups empty (because I don't or don't want this info) the results
comes up empty.

Any help is appreciated.
Thanks

It's with some hesitation that I'll disagree with my friends John and Jeff,
but the LIKE operator isn't the only way to do this... and it can have some
problems (e.g. if you're looking for last name "Johns" you'll find "Johnson").

An alternative is to have a Form named frmCrit with three textboxes -
txtFindTown, txtFindLast, txtFindLot. Your Query would then have a criterion
on Town of

=[Forms]![frmCrit]![txtFindTown] OR [Forms]![frmCrit]![txtFindTown] IS NULL

and similarly for the other two fields.

If you need more fields then this, the query can get awfully complicated,
especially if you go from query grid view to SQL view and back. There are
"query by form" methods that can actually build the SQL string of a query
dynamically, based on which form controls are filled in and which are null.