Help with Access Query Condition

[Guest]

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]

 

[SteveS]

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]

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

 

[Guest]

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?

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]

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

 

[SteveS]

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?

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.

 

[John Spencer]

If the fields Requestor(?) CallType(?), and Closed(?) are never null you can
use something like the following.

[Requestor] like Nz([forms]![report selector]![requestor],"*") or
[CallType] like Nz([forms]![report selector]![calltype],"*") or
[Closed] like Nz([forms]![report selector]![closed] ,"*")

Or you can force the three fields to have a value by wrapping them in the NZ
function also. This means that any indexes on those fields won't be used
for this criteria and the searches will probably be slower. The difference
in performance may not be noticeable - depends on your setup and the size of
the tables involved and other factors.