Multiple Criteria in a query

[Guest]

I have a query with several fields that I need to pull out certain
information from. The fields are all expiration dates for various items. I
need to show all data <= certain dates, but unless all fields meet the
criteria the query comes back blank.

 

[Guest]

If you require all fields to meet the criteria, then use <= SpecificDate on
the same row for all fields in the criteria section. If you want all data if
ANY of the fields meet the criteria, then each instance of the criteria needs
to be on a separate row - notice the word OR on the left side of your query
design. You are limited to 9 separate lines of criteria in an OR situation
(1 or 2 or 3 or 4, etc.). If you have more than 9, then create two queries
of exactly the same information, but break up your criteria into sets of 9 or
less. Use a union query to join the individual queries.

 

[Guest]

Thanks, it worked.

If you require all fields to meet the criteria, then use <= SpecificDate on
the same row for all fields in the criteria section. If you want all data if
ANY of the fields meet the criteria, then each instance of the criteria needs
to be on a separate row - notice the word OR on the left side of your query
design. You are limited to 9 separate lines of criteria in an OR situation
(1 or 2 or 3 or 4, etc.). If you have more than 9, then create two queries
of exactly the same information, but break up your criteria into sets of 9 or
less. Use a union query to join the individual queries.

 

[tina]

You are limited to 9 separate lines of criteria in an OR situation

(1 or 2 or 3 or 4, etc.). If you have more than 9, then create two queries
of exactly the same information, but break up your criteria into sets of 9 or
less. Use a union query to join the individual queries.

there are nine rows in the criteria grid in query Design view as a default,
but you are *not* limited to those nine rows. to add more rows to the grid,
choose Insert | Rows from the menu bar in Design view.

hth

 

[Guest]

SWEET!!!!! You have no idea the amount of work you just saved me on another
project!

THANK YOU!

 

[Jamie Collins]

design. You are limited to 9 separate lines of criteria in an OR situation
(1 or 2 or 3 or 4, etc.).

There is no such limit, as you have now discovered, but FWIW Access/
Jet is optimized to favour the IN operator e.g.

rather than

FieldName = 1 OR FieldName = 2 OR FieldName = 2 OR FieldName = 4

instead use

FieldName IN (1, 2, 3, 4)

Easier to read too, IMO.

Jamie.

--

 

[Guest]

Jimmy,

I think the others have answered your immediate problem, so I'll skip that.
What concerns me is your comment:

The fields are all expiration dates for various items.

This implies to me that your database is actually setup like a spreadsheet.
The problem with using a database like a spreadsheet is that it make updating
queries and forms, and reports a nightmare. With this structure, what
happens to all these things when you want to add another item (you have to
add another column to the table and adjust every query, form, or report that
might need that information).

In most normalized databases, you would only find one "ExpirationDate" data
field, and would be able to test multiple "items" by checking that single
field. Can you post a little more about your table structure? We might be
able to help you normalize it and save you some work down the road.

Dale

 

[tina]

you're very welcome
remember that what Access executes is the SQL statement (take a look at the
SQL pane of any query that you've written - View | SQL View from the menu
bar). the design grid is just a nice user interface for those of us who are
not - or not very - SQL literate. (i fall into the grey area between "not"
and "not very", myself. <g>) SQL is much more powerful, and flexible, than
the relatively simple statements generated when we use the design grid
exclusively.

 

[tina]

good catch, Dale! i hope the op posts back for more assistance.

 

[Guest]

good catch, Dale!

Let me see if I've got this straight: if two entity types have an attribute
with the same name then those entities must, at some 'superclass' level, *be*
the same? A 'bicycle' has a 'frame' and a 'beehive' has a 'frame' therefore
at some level a 'bicycle' is the same as a 'beehive', right?

I did a google search for "has an expiry date" and the first two hits turned
up entity types "The 08 Card" (the *official* Capital of Culture membership
card) and "laser toner" respectively. So you think "The 08 Card" and "laser
toner" share the same superclass, presumably "Things that expire on a known
date"? I'm not so sure myself... I think you may be confusing the concepts
"has a" and "is a".

Jamie.

--