Link to home
Start Free TrialLog in
Avatar of mcnuttlaw
mcnuttlawFlag for United States of America

asked on

Test for a checkbox value in either True/False state

My situation...

DESIGN
=====

I'm putting together a query in the query designer called qryClaims:

Field             Table     Total         Show
Client           Claims   Group By   checked
CaseName   Claims   Group By   checked
cbShow        Claims   Group By   checked


TEST
===

I double click on the query to see if it runs and as expected, it shows two lines for each case because of cbShow (yes/no).  

It shows one line for true and one line for false.  

Client   CaseName    ...other fields...               cbShow
15141   Joe's BMW    ...other dataset one...   checkbox (checked)
15141   Joe's BMW    ...other dataset two...   checkbox (not checked)


REPORTS
======

My reports are set to record source qryClaims.

Statements are passed to filter the report depending on user selection:

([Client] = '15141') And ([cbShow] = True)    << works
([Client] = '15141') And ([cbShow] = False)   << works
([Client] = '15141') And ([cbShow] = True  Or [cbShow] = False)  << does not work
([Client] = '15141')   << does not work

WHAT'S HAPPENING
==============

The third and fourth example should filter to the report ALL records for the case regardless of the state of the checkbox.  In other words, show all records on the report.

The actual result that the "other dataset one" in the above test is being filtered to the report, most likely because it is the first line that matches the statement [Client] = '15141'.   Both datasets should be filtered, should they not?

So the question is what should I use (or do) to get all records filtered to the report using the above query (qryClaims)?
Avatar of Ryan
Ryan
Flag of United States of America image

Just leave cbshow out of the filter criteria all together? Unless you're trying to keep out Nulls.

What do you mean "does not work"?

You're using filter, make sure filterOn=true.
Client is a string not a number? that 4th filter one should work.
try:

([Client] = '15141') And ([cbShow] = [cbShow])
For third and fouth lines, follwing should work:

[Client] = '15141'

if it doesn't, then try:

[Client] = '15141' And [cbShow] = [cbShow]

Mike
Avatar of mcnuttlaw

ASKER

Neither option worked.  Both shows only "dataset one".
I've even tried...

([Client] = '15141') And ([cbShow] = True) And ([Client] = '15141') And ([cbShow] = False)

Same result.  It's showing only "dataset one" and not both datasets.
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Theres a WHERE instead of FILTER when you open a form using docmd.
Otherwise, put the WHERE into your query. If you can't edit the query (because its used elsewhere), create a new query in the forms recordset.

These are workarounds for your problem, I don't see why Filter won't work.

Did you try putting filter in quotes?  "([Client] = '15141')"
Using filter, if you must:

="([Client] = '15141') And Switch([cbShow] = 'Show All',[Show], [cbShow] = 'Show True',True,1=1,False)"

Where:

cboShow    '<-- assuming your combo box
======== ' has data like this:
Show True
Show False
Show Both


It's a check box, fool. Had me thinking there I was and idiot and wrong.

Is your checkbox by any chance returning Null?

I still don't see why the checkbox is in the filter at all if its not part of the filtering. It's just another field.
The checkbox is part of the filtering.

The user has a choice to filter True, False or All.  True and False is working.  It's the All option that is not working.
re:> The user has a choice to filter True, False or All.  True and False is working.

I did two timw as how the data in cboShow is displayed. Could you comment on that. If you don't comment, I keep assuming that my assumed data for cboShow is:

cboShow    '<-- assuming your combo box
======== ' has data like this:
Show True
Show False
Show Both

What is the row source of cboShow
What are the number of columns
what is the bound field number for cboshow
What are the width of the columns

Regards,

Mike

Good news Mike.  Your solution worked!

At first it didn't work.  I had to step away from it and come back with a fresh set of eyes to see that I was still passing "[cbShow] = False" Or "[cbShow] = True" through the filter.

So now I pass only the client ([Client] = '15141') for all choices (True, False, All) through the filter and let your function take it from there.  The resulting report correctly shows the proper filtering.


I'm confused on this point of yours...

>make sure to have me.requery or me!subForm.Requery (whichever is applicable) in the afterupdate of the cboShow.

The form calling the report is an unbound form so I assume this does not apply?
I am sorry, I was thinking you are filtering a form for a while. This sort of confusion on my part is due to the fact I was dealing with quite few questions at the same time.

Thanks for the points and the grade.

Mike