asked on

Access Form - Filter a query for a Form List to include or exclude items based upon a checkbox value

Hello
I have an Access Form that has a list based upon a query that the user will select a job from a list. I am trying to filter that list to either include or exclude jobs that are "Closed" based upon the user's preference.

The Form is named
frmJCJobStatusCurrent

The checkbox is named:
ckbxIncludeClosedJobs

The user checks that box to Include the "Closed" jobs or leaves it blank to Exclude them.

My query is as follows:

SELECT tblQBCustomer.REFNUM, tblQBCustomer.NAME, tblQBCustomer.[Active Status T/F]
FROM tblQBCustomer
WHERE (((tblQBCustomer.[Active Status T/F])=0 Or (tblQBCustomer.[Active Status T/F])=[Forms]![frmJCJobStatusCurrent]![ckbxIncludeClosedJobs]));

Trouble is it either Includes Only the Closed Jobs or All Jobs. I want it to include only OPEN jobs when blank or all jobs when checked. It's working backwards. I've tried changing the 0 to -1 but that doesn't work either.

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wlwebb

ASKER

The field [Active Status T/F] is a T/F checkbox with Unchecked = Closed; Checked = Open

I updated my query logic to :

SELECT tblQBCustomer.REFNUM, tblQBCustomer.NAME, tblQBCustomer.[Active Status T/F]
FROM tblQBCustomer
WHERE (((tblQBCustomer.[Active Status T/F])=0 Or [Forms]![frmJCJobStatusCurrent]![ckbxIncludeClosedJobs]=True));

Still don't get a correct query result when the checkbox is left blank

wlwebb

ASKER

Found it.
(tblQBCustomer.[Active Status T/F])=0 should have been
(tblQBCustomer.[Active Status T/F])=True

wlwebb

ASKER

Though peter didn't exactly state the ultimate problem, his answer led me to the value issue of using zero instead of True