Need help with Criteria for Queries Pls

[Matt]

I am trying to run a query based on criteria in a loaded form. I have
multiple fields on the form which I am basing the criteria on.

eg. field 1 "Staff memeber" = John Smith, and field 2 "customers" = ABC
inc

this works fine finding all transactions with these 2 criteria, but I would
like to be able to have the option of leaving either of the fields out of
the "search" eg. to show all customers related to John smith

Is this possible to do usings queries in this way? When I try leaving one
of the fields blank at the moment, the query finds nothing.

Any help would be greatly Appreciated
Thanks in advance
Matt

 

[John Viescas]

The general technique is:

SELECT *
FROM MyTable
WHERE (MyTable.Customer = [Forms]![MyForm]![CustSearch])
OR
([Forms]![MyForm]![CustSearch] IS NULL)

However, you can get "query too complex" if you include more than 3 or 4 of
these in a query because the JET compiler "optimizes" this into a set of
ORed ANDs - that can result in a huge WHERE clause.

If you run into this, the only solution is to build the filter on the fly in
code behind the form - ignoring the criteria that is empty or null.

--
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)

 

[John Vinson]

I am trying to run a query based on criteria in a loaded form. I have
multiple fields on the form which I am basing the criteria on.

eg. field 1 "Staff memeber" = John Smith, and field 2 "customers" = ABC
inc

Well... just a warning: using staff member *names* as a key is risky.
Names are not unique; I used to work with Dr. Lawrence David Wise and
his colleague, Dr. Lawrence David Wise. You may want to have a StaffID
as a unique identifier (and use a combo box to select the John Smith
that works in Department A or the John Smith in Department J).

this works fine finding all transactions with these 2 criteria, but I would
like to be able to have the option of leaving either of the fields out of
the "search" eg. to show all customers related to John smith

Is this possible to do usings queries in this way? When I try leaving one
of the fields blank at the moment, the query finds nothing.

Try a criterion like

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

for the two fields. If the form control is left empty you'll get all
records for that field.