multiple criteria

  • Thread starter Thread starter Shiner452
  • Start date Start date
S

Shiner452

I have a query with 15+ fields and I am making 3 different reports that
people can look up certain records based on criteria that they specify
in a form. Here is an example of my problem. I have fldPotSize
fldPotWeek. Both fields ask to specify criteria through a form.
However, if I want to look up plants by fldPotSize and dont care about
fldPotWeek (I want all Pot Weeks listed) I am unable to do so. Arent I
supposed to say 'If Null' or something? Does this make sense? Any
help would be appreciated...you can email me at (e-mail address removed).

Thanks
 
The general technique is to construct a predicate like this:

WHERE ((fldPotSize = [Enter pot size you want:]) OR ([Enter pot size you
want:] IS NULL))

But you can string together only 3 or 4 of these in a query before you get
"query too complex" because the query parser "normalizes" a series of these
into ORed ANDs instead of ANDed ORs. The real solution is to prompt for the
parameters in a custom form and build the filter using only the values
entered. If they don't enter a pot size, don't build a criteria at all.

P.S. Messages from ExcelTip.com seem to be posting multiple times. I hope
you get this answer.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top