• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 467
  • Last Modified:

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.


0
wlwebb
Asked:
wlwebb
  • 3
1 Solution
 
peter57rCommented:
I can't tell from your Q how you identify whether a job is open or closed.


But the query logic you need is..

SELECT tblQBCustomer.REFNUM, tblQBCustomer.NAME, tblQBCustomer.[Active Status T/F]
FROM tblQBCustomer
WHERE [Active Status T/F]=<value when open>  Or  Forms]![frmJCJobStatusCurrent]![ckbxIncludeClosedJobs]= True;

0
 
wlwebbAuthor Commented:
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

0
 
wlwebbAuthor Commented:
Found it.
(tblQBCustomer.[Active Status T/F])=0 should have been
(tblQBCustomer.[Active Status T/F])=True
0
 
wlwebbAuthor Commented:
Though peter didn't exactly state the ultimate problem, his answer led me to the value issue of using zero instead of True
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now