Filtering Reports

[Guest]

I have a report that uses a form to filter what is shown. The form has 3
check boxs: 1 for customers that want to be called, 1 for the customers that
do not have to sign, and one to show customers that have been archived. All
three options come from the customer table and are yes/no controls. I have a
form that you check one of the latter options and the report SHOULD show
those records. How do you do that? (ie. show all records that have the
contact control checked, and/or all records that have the dontsign box
checked, and/or all record that have the archive box checked.)

 

[Jeff Boyce]

James

One approach would be to use parameters in a query, and the query as the
source for the report. The parameters would be on the three fields you
described, and would look something like;

Forms!YourFilterForm!chkYourFirstYesNoControl
...

Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

That's what I have, but if nothing is checked I want it to show all records.
Here is my sql statement:

SELECT CustomerInfo.*, [CustomerInfo].[Contact], [CustomerInfo].[OptOut],
[CustomerInfo].[Archive] FROM CustomerInfo WHERE
((([CustomerInfo].[Contact])=[forms].[report options].[contact]) And
(([CustomerInfo].[OptOut])=[forms].[report options].[optout]) And
(([CustomerInfo].[Archive])=[forms].[report options].[archive]));

 

[Jeff Boyce]

James

Is the SQL statement from your query?

Have you looked at the "Like" operator for criteria? If you used Like
[Forms]![....] & *, would that handle the no value? Probably not, since
Yes/No can only have two states!

You may have to build your SQL statement dynamically, on the form itself,
"behind" the command button, and leave out the WHERE clause(s) if there's no
check. But how will you/users know if no mark means "look for a 'No'", or
"no value given"?

Jeff Boyce
<Access MVP>

That's what I have, but if nothing is checked I want it to show all records.
Here is my sql statement:

SELECT CustomerInfo.*, [CustomerInfo].[Contact], [CustomerInfo].[OptOut],
[CustomerInfo].[Archive] FROM CustomerInfo WHERE
((([CustomerInfo].[Contact])=[forms].[report options].[contact]) And
(([CustomerInfo].[OptOut])=[forms].[report options].[optout]) And
(([CustomerInfo].[Archive])=[forms].[report options].[archive]));

I have a report that uses a form to filter what is shown. The form has 3
check boxs: 1 for customers that want to be called, 1 for the customers that
do not have to sign, and one to show customers that have been archived. All
three options come from the customer table and are yes/no controls. I have a
form that you check one of the latter options and the report SHOULD show
those records. How do you do that? (ie. show all records that have the
contact control checked, and/or all records that have the dontsign box
checked, and/or all record that have the archive box checked.)

 

[John Spencer (MVP)]

The following MAY work for you.

SELECT CustomerInfo.*
FROM CustomerInfo
WHERE [CustomerInfo].[Contact]=[forms]![report options]![contact]

And [CustomerInfo].[OptOut])=[forms]![report options]![optout]

And [CustomerInfo].[Archive])=[forms]![report options]![archive]

OR (
[forms]![report options]![contact] = False AND
[forms]![report options]![optout] = False AND
[forms]![report options]![archive] = False
)

This will return all records if your three controls are false.

If you check one box and not the other two, then you will get the records that
have only the one box checked and the other two not checked. Is that what you want?

That's what I have, but if nothing is checked I want it to show all records.
Here is my sql statement:

SELECT CustomerInfo.*, [CustomerInfo].[Contact], [CustomerInfo].[OptOut],
[CustomerInfo].[Archive] FROM CustomerInfo WHERE
((([CustomerInfo].[Contact])=[forms].[report options].[contact]) And
(([CustomerInfo].[OptOut])=[forms].[report options].[optout]) And
(([CustomerInfo].[Archive])=[forms].[report options].[archive]));

I have a report that uses a form to filter what is shown. The form has 3
check boxs: 1 for customers that want to be called, 1 for the customers that
do not have to sign, and one to show customers that have been archived. All
three options come from the customer table and are yes/no controls. I have a
form that you check one of the latter options and the report SHOULD show
those records. How do you do that? (ie. show all records that have the
contact control checked, and/or all records that have the dontsign box
checked, and/or all record that have the archive box checked.)

 

[Guest]

ohn, you are real close! It works for the most part. However, If both
contact and optout are true on a record and you check contact, the report
will return no records. If you check both contact and optout it returns the
records. What would you put in the sql staement to get the contact records
no matter what other controls are set to true?

The following MAY work for you.

SELECT CustomerInfo.*
FROM CustomerInfo
WHERE [CustomerInfo].[Contact]=[forms]![report options]![contact]

And [CustomerInfo].[OptOut])=[forms]![report options]![optout]

And [CustomerInfo].[Archive])=[forms]![report options]![archive]

OR (
[forms]![report options]![contact] = False AND
[forms]![report options]![optout] = False AND
[forms]![report options]![archive] = False
)

This will return all records if your three controls are false.

If you check one box and not the other two, then you will get the records that
have only the one box checked and the other two not checked. Is that what you want?

That's what I have, but if nothing is checked I want it to show all records.
Here is my sql statement:

SELECT CustomerInfo.*, [CustomerInfo].[Contact], [CustomerInfo].[OptOut],
[CustomerInfo].[Archive] FROM CustomerInfo WHERE
((([CustomerInfo].[Contact])=[forms].[report options].[contact]) And
(([CustomerInfo].[OptOut])=[forms].[report options].[optout]) And
(([CustomerInfo].[Archive])=[forms].[report options].[archive]));

I have a report that uses a form to filter what is shown. The form has 3
check boxs: 1 for customers that want to be called, 1 for the customers that
do not have to sign, and one to show customers that have been archived. All
three options come from the customer table and are yes/no controls. I have a
form that you check one of the latter options and the report SHOULD show
those records. How do you do that? (ie. show all records that have the
contact control checked, and/or all records that have the dontsign box
checked, and/or all record that have the archive box checked.)

 

[Jeff Boyce]

James (and John)

I'll jump back in...

As long as you are using a WHERE clause that refers to all three controls,
the query will check the values of all three and use ALL THREE. If you want
to only use two, your SQL statement needs to have one few WHERE
(sub-)clauses. Do this by creating code behind your <Do it> command button
that inspects each control and generates the next portion of the WHERE
clause.

And I repeat, how would you, your user, and Access know if leaving a control
unchecked means you want any records without regard for the field underlying
that control, or if that means you want the records whose value is False for
that control's field?

--
Good luck

Jeff Boyce
<Access MVP>

ohn, you are real close! It works for the most part. However, If both
contact and optout are true on a record and you check contact, the report
will return no records. If you check both contact and optout it returns the
records. What would you put in the sql staement to get the contact records
no matter what other controls are set to true?

The following MAY work for you.

SELECT CustomerInfo.*
FROM CustomerInfo
WHERE [CustomerInfo].[Contact]=[forms]![report options]![contact]

And [CustomerInfo].[OptOut])=[forms]![report options]![optout]

And [CustomerInfo].[Archive])=[forms]![report options]![archive]

OR (
[forms]![report options]![contact] = False AND
[forms]![report options]![optout] = False AND
[forms]![report options]![archive] = False
)

This will return all records if your three controls are false.

If you check one box and not the other two, then you will get the records that
have only the one box checked and the other two not checked. Is that what you want?

That's what I have, but if nothing is checked I want it to show all records.
Here is my sql statement:

SELECT CustomerInfo.*, [CustomerInfo].[Contact], [CustomerInfo].[OptOut],
[CustomerInfo].[Archive] FROM CustomerInfo WHERE
((([CustomerInfo].[Contact])=[forms].[report options].[contact]) And
(([CustomerInfo].[OptOut])=[forms].[report options].[optout]) And
(([CustomerInfo].[Archive])=[forms].[report options].[archive]));

:

I have a report that uses a form to filter what is shown. The form has 3
check boxs: 1 for customers that want to be called, 1 for the customers that
do not have to sign, and one to show customers that have been archived. All
three options come from the customer table and are yes/no controls. I have a
form that you check one of the latter options and the report SHOULD show
those records. How do you do that? (ie. show all records that have the
contact control checked, and/or all records that have the dontsign box
checked, and/or all record that have the archive box checked.)

 

[Guest]

You would have a text box that says: "To view all records do not select an
option." or something like that!

James (and John)

I'll jump back in...

As long as you are using a WHERE clause that refers to all three controls,
the query will check the values of all three and use ALL THREE. If you want
to only use two, your SQL statement needs to have one few WHERE
(sub-)clauses. Do this by creating code behind your <Do it> command button
that inspects each control and generates the next portion of the WHERE
clause.

And I repeat, how would you, your user, and Access know if leaving a control
unchecked means you want any records without regard for the field underlying
that control, or if that means you want the records whose value is False for
that control's field?

--
Good luck

Jeff Boyce
<Access MVP>

ohn, you are real close! It works for the most part. However, If both
contact and optout are true on a record and you check contact, the report
will return no records. If you check both contact and optout it returns the
records. What would you put in the sql staement to get the contact records
no matter what other controls are set to true?

The following MAY work for you.

SELECT CustomerInfo.*
FROM CustomerInfo
WHERE [CustomerInfo].[Contact]=[forms]![report options]![contact]

And [CustomerInfo].[OptOut])=[forms]![report options]![optout]

And [CustomerInfo].[Archive])=[forms]![report options]![archive]

OR (
[forms]![report options]![contact] = False AND
[forms]![report options]![optout] = False AND
[forms]![report options]![archive] = False
)

This will return all records if your three controls are false.

If you check one box and not the other two, then you will get the records that
have only the one box checked and the other two not checked. Is that what you want?

James wrote:

That's what I have, but if nothing is checked I want it to show all records.
Here is my sql statement:

SELECT CustomerInfo.*, [CustomerInfo].[Contact], [CustomerInfo].[OptOut],
[CustomerInfo].[Archive] FROM CustomerInfo WHERE
((([CustomerInfo].[Contact])=[forms].[report options].[contact]) And
(([CustomerInfo].[OptOut])=[forms].[report options].[optout]) And
(([CustomerInfo].[Archive])=[forms].[report options].[archive]));

:

I have a report that uses a form to filter what is shown. The form has 3
check boxs: 1 for customers that want to be called, 1 for the customers that
do not have to sign, and one to show customers that have been archived. All
three options come from the customer table and are yes/no controls. I have a
form that you check one of the latter options and the report SHOULD show
those records. How do you do that? (ie. show all records that have the
contact control checked, and/or all records that have the dontsign box
checked, and/or all record that have the archive box checked.)

 

[Jeff Boyce]

James

My point is NOT for the users. My point is for you and the code. Unless
you build the SQL statement "on the fly", any hard-coded references to the
controls (options) will use the values found there. For checkboxes, the
only values it will find will be True or False, right?

--
Good luck

Jeff Boyce
<Access MVP>

You would have a text box that says: "To view all records do not select an
option." or something like that!

James (and John)

I'll jump back in...

As long as you are using a WHERE clause that refers to all three controls,
the query will check the values of all three and use ALL THREE. If you want
to only use two, your SQL statement needs to have one few WHERE
(sub-)clauses. Do this by creating code behind your <Do it> command button
that inspects each control and generates the next portion of the WHERE
clause.

And I repeat, how would you, your user, and Access know if leaving a control
unchecked means you want any records without regard for the field underlying
that control, or if that means you want the records whose value is False for
that control's field?

--
Good luck

Jeff Boyce
<Access MVP>

ohn, you are real close! It works for the most part. However, If both
contact and optout are true on a record and you check contact, the report
will return no records. If you check both contact and optout it

returns
the

records. What would you put in the sql staement to get the contact records
no matter what other controls are set to true?

:

The following MAY work for you.

SELECT CustomerInfo.*
FROM CustomerInfo
WHERE [CustomerInfo].[Contact]=[forms]![report options]![contact]

And [CustomerInfo].[OptOut])=[forms]![report options]![optout]

And [CustomerInfo].[Archive])=[forms]![report options]![archive]

OR (
[forms]![report options]![contact] = False AND
[forms]![report options]![optout] = False AND
[forms]![report options]![archive] = False
)

This will return all records if your three controls are false.

If you check one box and not the other two, then you will get the records that
have only the one box checked and the other two not checked. Is

that
what you want?

James wrote:

That's what I have, but if nothing is checked I want it to show

all
records.

Here is my sql statement:

SELECT CustomerInfo.*, [CustomerInfo].[Contact], [CustomerInfo].[OptOut],
[CustomerInfo].[Archive] FROM CustomerInfo WHERE
((([CustomerInfo].[Contact])=[forms].[report options].[contact]) And
(([CustomerInfo].[OptOut])=[forms].[report options].[optout]) And
(([CustomerInfo].[Archive])=[forms].[report options].[archive]));

:

I have a report that uses a form to filter what is shown. The

form
has 3

check boxs: 1 for customers that want to be called, 1 for the customers that
do not have to sign, and one to show customers that have been archived. All
three options come from the customer table and are yes/no

controls.
I have a

form that you check one of the latter options and the report

SHOULD
show

those records. How do you do that? (ie. show all records that

have
the

contact control checked, and/or all records that have the

dontsign
box

checked, and/or all record that have the archive box checked.)