[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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

I have created a form that is used by users to select criteria for a query.  Withthe form they can select things like a date range, county, staff person and so on.  There are a handfull of fields that are yes/no fields in the table.  I've displayed them on the form as check boxes and want them to be able to check as many as they need.  However in the query, I want these checkboxes to be evaluated as OR statements.  For example, if they select Targeted and PRC, the report will print any record that is marked as Targeted and/or PRC.  I've probably been looking at this too long, but I can only get it to use AND.  So if my user selects more than one yes/no field, they all hae to be marked as "yes" in the record for it to display.

There are 7 yes/not fields on this form that could potentially be checked.


This is the text in my query:
IIf([Forms]![DialogTATracking]![YesTargeted]=-1,[tblOhioProviders].[Targeted]=[Forms]![DialogTATracking]![YesTargeted],[tblOhioProviders].[Targeted]-1)

The SQL is huge, but here is a bit of the "WHERE" portion:
(IIf([Forms]![DialogTATracking]![YesPRC]=-1,[tblOhioProviders].[PRC]=[Forms]![DialogTATracking]![YesPRC],[tblOhioProviders].[PRC]-1))<>False) AND ((IIf([Forms]![DialogTATracking]![YesTargeted]=-1,[tblOhioProviders].[Targeted]=[Forms]![DialogTATracking]![YesTargeted],[tblOhioProviders].[Targeted]-1))<>False)

Open in new window

0
Terri Alekzander
Asked:
Terri Alekzander
  • 7
  • 6
  • 5
  • +2
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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)
0
 
Rey Obrero (Capricorn1)Commented:
it might be better building your query sql statement using vba, based on the forms input textbox and selected check boxes..

0
 
Terri AlekzanderAuthor Commented:
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.  
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
0
 
mbizupCommented:
<< 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.
0
 
Terri AlekzanderAuthor Commented:
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?  
0
 
mbizupCommented:
<< 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...
0
 
Terri AlekzanderAuthor Commented:
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?
0
 
Dale FyeCommented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
mbizupCommented:
<I can upload something to you tomorrow.  Is that soon enough? >

Not a problem...
0
 
Terri AlekzanderAuthor Commented:
<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
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
0
 
Terri AlekzanderAuthor Commented:
"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.
0
 
mbizupCommented:
Heres an example of how to use VBA for this (which I think is more readable and easier to maintain).

I removed the part of your criteria in the query that deals with the checkboxes so that it can be handled through code.  This could be done with all of your criteria, but for the sake of this example, I left your query handling everything but the chaeckboxes, with the checkbox criteria handled through code.

Added a button to the form with the following VBA in it's Click event:

 
   Dim strSQL As String
   'This defines the criteria
    strSQL = "1"
    strSQL = strSQL & IIf(Me.YesTargeted = True, " AND [Targeted] = true", "")
    strSQL = strSQL & IIf(Me.YesFS = True, " AND [FS] = true", "")
    strSQL = strSQL & IIf(Me.YesPFS = True, " AND [PFS] = true", "")
    strSQL = strSQL & IIf(Me.YesPRC = True, " AND [PRC] = true", "")
    strSQL = strSQL & IIf(Me.YesRSS = True, " AND [RSS] = true", "")
    strSQL = strSQL & IIf(Me.YesSB6 = True, " AND [SB6] = true", "")
    strSQL = strSQL & IIf(Me.YesSFI = True, " AND [SFI] = true", "")
    
   ' This opens the report
    DoCmd.OpenReport "TATrackingDetail", acViewPreview, , strSQL

Open in new window


And also removed some of the Macros, so the functionality is a little different.

Open up the main form, select the criteria checkboxes, and click the "Open Report" button:


iTrack-test.accdb
0
 
Dale FyeCommented:
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", "")


0
 
Dale FyeCommented:
actually, I don't think that will work either, since the OP stated if none of the checkboxes is checked, they wanted all of the records.  But you cannot simply set the first line to

strSQL = "1"

Because that would give you all of the records, regardless.  How about:

    strSQL = ""
    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", "")

    if len(strSQL) > 0 then strSQL = mid(strSQL, 4)
0
 
mbizupCommented:
My understanding is that if multiple checkboxes are selected ALL selected criteria need to apply (AND); if none are checked, all records are shown.
0
 
mbizupCommented:
Actually re-reading it... I think you're right.
0
 
Dale FyeCommented:
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.
0
 
mbizupCommented:
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.
0
 
Terri AlekzanderAuthor Commented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
:-)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 6
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now