[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 958
  • Last Modified:

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)?
0
mcnuttlaw
Asked:
mcnuttlaw
  • 6
  • 4
  • 3
1 Solution
 
RyanProject Engineer, ElectricalCommented:
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.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
try:

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

[Client] = '15141'

if it doesn't, then try:

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

Mike
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
mcnuttlawAuthor Commented:
Neither option worked.  Both shows only "dataset one".
0
 
mcnuttlawAuthor Commented:
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.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Instead of filter, revise your SQL like:

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

Under field [cbShow], in its criteria cell enter:

IIF(fnShow()="All", [cbShow], fnShow())

And, coppy the following code in to a module:

Function fnShow() As Variant
On Error GoTo 10
 
  Masgbox Forms!FormName!cboShow '<-- remove after you make sure
                                                             ' it is able to read the value from
                                                             'xboShow
 
  Select Case Nz(Forms!FormName!cboShow ,"All")
     Case "Show True"
           fnShow = True
     Case "Show False"
           fnShow = False
     else
           fnShow = "All"
  End Select

Exit Function
10:

  fnShow = "All"

End Function

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

Mike

0
 
RyanProject Engineer, ElectricalCommented:
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')"
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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


0
 
RyanProject Engineer, ElectricalCommented:
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.
0
 
mcnuttlawAuthor Commented:
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.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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

0
 
mcnuttlawAuthor Commented:
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?
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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