Access | Using form to select parameters for query | multiple Yes/No fields

Seems for starters you would want to be using OR instead of AND below.

(IIf([Forms]![DialogTATracking]![YesPRC]=-1,[tblOhioProviders].[PRC]=[Forms]![DialogTATracking]![YesPRC],[tblOhioProviders].[PRC]-1))<>False) OR ((IIf([Forms]![DialogTATracking]![YesTargeted]=-1,[tblOhioProviders].[Targeted]=[Forms]![DialogTATracking]![YesTargeted],[tblOhioProviders].[Targeted]-1))<>False)

it might be better building your query sql statement using vba, based on the forms input textbox and selected check boxes..

DatabaseMX: I had never actually looked at the SQL view before posting this question.  I work in the query builder.  If I edit the SQL statement that is there and just change all the ANDs to ORs will that solve the issue?

Capricorn1: I do not know enough vba to do that.  

"will that solve the issue?"
I can't say for sure ... but seems that would be a start.  

If you want to do the change quickly, you can use this:

http://www.rickworld.com/products.html#Find%20and%20Replace%209.0

mx

<< all the ANDs to ORs will that solve the issue? >>

Be cautious with this, and make a backup first - if you do have a convoluted query it is entirely possible that not all of the ands should be changed.

I'd also suggest migrating this one to VBA.  There would be a learning curve involved, but the end result would be easier to read, and easier to maintain.

mbizup:  Good advice.  I did make a backup.  Using ORs is not the solution.  I'm willing to learn vba and recognize that it would be a more streamlined approach.  I've had no problem using forms for query filters, but up to this point, all the options have been text fields or combo boxes.  This one has me stumped.  In the example I describe in my original question, if the user checks Targeted, I only want to get the targeted records.  If the user selects PRC, I only want the PRC records.  If they select both, I want either to be true, but at least one of them.  Currently my query returns only the records that match both.  When I switched the appropriate AND statements to OR, the query returned everything regardless what was checked.

Any recommendation on a good reference, resource for getting started with this type of specific usage in vba?  

<< Any recommendation on a good reference, resource for getting started with this type of specific usage in vba? >>

There are plenty of good online references for getting started in VBA, but I can't think of anything specifically geared towards converting queries (or criteria) to VBA.  I'll keep thinking that one over for a bit...

In the meantime, is your WHERE clause currently dedicated exclusively to managing these checkboxes, or do you have other criteria in there as well?

Can you post a sample of your database with any sensitive data masked or removed?

Just trying to figure out the best approach here...

The WHERE Statement includes variables for a date range, staff name, county, funder and program name.  These are all working fine.  If the user selects any of all of these, the query filters appropirately.  If they are left blank, then All Records is assumed.  I can probably post a copy of the database but will need a little time to clear out all the junk so that you only have to look at what is necessary.  I can upload something to you tomorrow.  Is that soon enough?

The challenge of using a checkbox on a form to identify which records to include is what does an unchecked checkbox on the parameter form mean?

A checked checkbox normally means that you want to identify records where the associated field is True.  But does an unchecked checkbox mean that you want to identify records where the associated field is False, or does it mean that you don't want it to include that field in the critier string at all.

I generally prefer to use combo boxes when I filter on Yes/No fields, because I can include options for True (Yes) and False (No) or I can leave the combo boxes value empty (or NULL).  That way, I know exactly how to set the criteria for the selected field.

Ok ... based on this:

if the user checks Targeted, I only want to get the targeted records
If the user selects PRC, I only want the PRC records
If they select both, I want either to be true, but at least one of them.

I emulated that - using 4 check boxes, and using the SQL below, I seem to get those results - see images.

SELECT Table5.*
FROM Table5
WHERE (((Table5.PRC)=Switch([Forms]![DialogTATracking]![YesPRC]=True,True,1=1,Null))) OR (((Table5.Targeted)=Switch([Forms]![DialogTATracking]![YesTargeted]=True,True,1=1,Null))) OR (((Table5.TestChk3)=Switch([Forms]![DialogTATracking]![Chk3]=True,True,1=1,Null))) OR (((Table5.TestChk4)=Switch([Forms]![DialogTATracking]![Chk4]=True,True,1=1,Null)));

And if no check boxes are checked, no results are returned.

But one question I have is - for example this case:
If the user selects PRC, I only want the PRC records

Does that mean then, that NO other fields s/b checked ? IE .. ONLY PRC is checked and nothing else ?

mx
Capture1.gif
Capture2.gif
Capture3.gif
Capture4.gif
Capture5.gif

<I can upload something to you tomorrow.  Is that soon enough? >

Not a problem...

<And if no check boxes are checked, no results are returned.

If no check boxes are checked, then all records should be returned.  I want it to mean that they want all types of records and are not needing them to be limited by the codes.

<But one question I have is - for example this case:
If the user selects PRC, I only want the PRC records

Does that mean then, that NO other fields s/b checked ? IE .. ONLY PRC is checked and nothing else ?>

No.  These are not radio buttons that only allow one item to be selected.  The user can select any type.  I am wondering if the problem is in my design of the table.

I've attached a copy of the specific thing I'm trying to work out.  This file contains only the tables, form, query, macro and report that are affected.  This is probably like looking into the mind of a a mad-woman, but I hope my logic isn't too convoluted and you are able to make sense of it.  Self-taught has gotten me pretty far, but there are definitely areas like this where I see that I need some formal education in databases.

Thank you for all your help.  Seeing how you approached this in the screen shots above is very helpful.

iTrack-test.accdb

"If no check boxes are checked, then all records should be returned. "
Is that the only remaining issue at this point ?

fyi ... I have a 3 hr meeting now.

mx

"If no check boxes are checked, then all records should be returned. "
Is that the only remaining issue at this point ?

Yes, I believe that is the only issue outstanding.  Whenever you can get to this is fine.  If you knew how long I had worked on trying to figure it out you would fall out of your chair.

I thought the OP wanted "OR" tests, not "AND"  which would look like:

    strSQL = "0"
    strSQL = strSQL & IIf(Me.YesTargeted = True, " OR [Targeted] = true", "")
    strSQL = strSQL & IIf(Me.YesFS = True, " OR [FS] = true", "")
    strSQL = strSQL & IIf(Me.YesPFS = True, " OR [PFS] = true", "")
    strSQL = strSQL & IIf(Me.YesPRC = True, " OR [PRC] = true", "")
    strSQL = strSQL & IIf(Me.YesRSS = True, " OR [RSS] = true", "")
    strSQL = strSQL & IIf(Me.YesSB6 = True, " OR [SB6] = true", "")
    strSQL = strSQL & IIf(Me.YesSFI = True, " OR [SFI] = true", "")

My understanding is that if multiple checkboxes are selected ALL selected criteria need to apply (AND); if none are checked, all records are shown.

Actually re-reading it... I think you're right.

See note above (36982905):

if the user checks Targeted, I only want to get the targeted records
If the user selects PRC, I only want the PRC records
If they select both, I want either to be true, but at least one of them.

fyed's modification to my code should do the trick  -

If you're not familiar with VBA, post back if you need instructions for changing it in the the sample.

Thank you so much! I appreciate your time and expertise in helping out with this.  It looks pretty straight forward to me.  If I need further assistance with modifying the vba, I'll let you know.

:-)