Help with Access Query Condition

A

AmytDev

Hi. I am trying to create a query that is filtered from a form. The form has
3 filters and I want to be able to pull a report depending on what the user
chooses to filter the report by. For example, the filters on the form are
Requestor, Calltype, and Closed. If a user doesn't select any, the report is
to bring all records from the table. If a user selects only one, the report
is to bring in all records that pertains to that fields. And if the user
selects something from each, the report is filtered with each. Any ideas?

I have this for each fields as the condition so far but it doesn't account
for if the user doesn't select any filters:
like [forms]![report selector]![requestor] or like [forms]![report
selector]![calltype] or like [forms]![report selector]![closed]
 
S

SteveS

AmytDev said:
Hi. I am trying to create a query that is filtered from a form. The form has
3 filters and I want to be able to pull a report depending on what the user
chooses to filter the report by. For example, the filters on the form are
Requestor, Calltype, and Closed. If a user doesn't select any, the report is
to bring all records from the table. If a user selects only one, the report
is to bring in all records that pertains to that fields. And if the user
selects something from each, the report is filtered with each. Any ideas?

I have this for each fields as the condition so far but it doesn't account
for if the user doesn't select any filters:
like [forms]![report selector]![requestor] or like [forms]![report
selector]![calltype] or like [forms]![report selector]![closed]
Click to expand...

Look at this site:

http://www.mvps.org/access/queries/qry0001.htm


The criteria should look something like this:

[forms]![report selector]![requestor] or [forms]![report selector]![requestor]
Is Null Or [forms]![report selector]![calltype] or [forms]![report
selector]![calltype] Is Null Or [forms]![report selector]![closed] OR
[forms]![report selector]![closed] Is Null


HTH
 
A

AmytDev

Unfortunately, that didn't work. I selected one of the choices in the combo
box of the 3 combo boxes and left the others blank and received that it is
too complex to be evaluated. Anything else I could do?

SteveS said:
AmytDev said:
Hi. I am trying to create a query that is filtered from a form. The form has
3 filters and I want to be able to pull a report depending on what the user
chooses to filter the report by. For example, the filters on the form are
Requestor, Calltype, and Closed. If a user doesn't select any, the report is
to bring all records from the table. If a user selects only one, the report
is to bring in all records that pertains to that fields. And if the user
selects something from each, the report is filtered with each. Any ideas?

I have this for each fields as the condition so far but it doesn't account
for if the user doesn't select any filters:
like [forms]![report selector]![requestor] or like [forms]![report
selector]![calltype] or like [forms]![report selector]![closed]
Click to expand...

Look at this site:

http://www.mvps.org/access/queries/qry0001.htm


The criteria should look something like this:

[forms]![report selector]![requestor] or [forms]![report selector]![requestor]
Is Null Or [forms]![report selector]![calltype] or [forms]![report
selector]![calltype] Is Null Or [forms]![report selector]![closed] OR
[forms]![report selector]![closed] Is Null


HTH
Click to expand...
 
S

SteveS

AmytDev said:
Unfortunately, that didn't work. I selected one of the choices in the combo
box of the 3 combo boxes and left the others blank and received that it is
too complex to be evaluated. Anything else I could do?
Click to expand...



1) When the report opens, set the report recordsource by building the query
(SQL) in VBA.

2) If you are using DoCmd.OpenReport , you can set [wherecondition] or
[filtername] using VBA

DoCmd.OpenReport reportname,, filtername

or

DoCmd.OpenReport reportname,,, wherecondition

build the filtername or wherecondition then open the report.
 
Top