Results 1 to 10 of 10
  1. #1
    Oblio is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    50

    Return all records if each Region Number option button has not been selected OR return records based

    Hi,

    I have a query named q_Region_Status which is based on a single table named t_Region_Status.
    Click image for larger version. 

Name:	t_Region_Status.jpg 
Views:	18 
Size:	145.6 KB 
ID:	17821

    An unbound parameter form contains 8 radio buttons numbered 1 thru 8 for the regions. Each control is named [Reg1], [Reg2] [Reg3] etc
    in addition to a Status area containing 8 radio buttons named: ACTIVE, INTERIM, PROBATION etc (the same name as their respective label on the form)
    Click image for larger version. 

Name:	Parameter Form.jpg 
Views:	18 
Size:	184.7 KB 
ID:	17818
    A minimum of 1 Status radio button MUST be chosen. Any combination of Region number buttons can be chosen, and I can get the query to return the proper records, HOWEVER, I also need to add a way to allow ALL Region Numbers if none of the region buttons have been selected.
    Here is the query SQL which works prior to the ALL Regions requirement:

    Code:
     Like IIf([Forms]![f_Choose_Regions_Status]![Reg1]=-1,1,"") Or Like IIf([Forms]![f_Choose_Regions_Status]![Reg2]=-1,2,"") Or Like IIf([Forms]![f_Choose_Regions_Status]![Reg3]=-1,3,"") Or Like IIf([Forms]![f_Choose_Regions_Status]![Reg4]=-1,4,"") Or Like IIf([Forms]![f_Choose_Regions_Status]![Reg5]=-1,5,"") Or Like IIf([Forms]![f_Choose_Regions_Status]![Reg6]=-1,6,"") Or Like IIf([Forms]![f_Choose_Regions_Status]![Reg7]=-1,7,"") Or Like IIf([Forms]![f_Choose_Regions_Status]![Reg8]=-1,8,"")
    Here is the query SQL used for Status:

    Code:
    Like IIf([Forms]![f_Choose_Regions_Status]![ACTIVE]=-1,"ACTIVE","") Or Like IIf([Forms]![f_Choose_Regions_Status]![INTERIM]=-1,"INTERIM","") Or Like IIf([Forms]![f_Choose_Regions_Status]![PROBATION]=-1,"PROBATION","") Or Like IIf([Forms]![f_Choose_Regions_Status]![ON LEAVE]=-1,"ON LEAVE","") Or Like IIf([Forms]![f_Choose_Regions_Status]![SUSPENDED]=-1,"SUSPENDED","") Or Like IIf([Forms]![f_Choose_Regions_Status]![RESIGNED]=-1,"RESIGNED","") Or Like IIf([Forms]![f_Choose_Regions_Status]![DE-CERTIFIED]=-1,"DE-CERTIFIED","") Or Like IIf([Forms]![f_Choose_Regions_Status]![DECEASED]=-1,"DECEASED","")
    Here is the query window:

    Click image for larger version. 

Name:	Query Design View.jpg 
Views:	18 
Size:	130.2 KB 
ID:	17819
    I would also like to add the option of the user to be able to click a command button that will mark the ODD Number Regions as selected, and another button for the Even Regions to be selected.

    Can someone please advise how to accomplish this? FYI, I did not wish to over complicate this, so I removed one other requirement I need to get to function as well...There is a field in the table named Qualification Set which contains one of the numbers 1 thru 6 from the lookup table named lt_Qualifications. The parameter form would also contain a combobox named cboQualifications with the first column bound...I am just not sure how to include the ability to include ALL possible Qualifications...

    Attached is a copy of the database without the Qualification tables. I can easily add that verson, however, I thought it might be easier to just concentrate on getting the ALL regions selected first.

    I have tried several things without any luck. In the query grid I have tried an Or Is Null to return all of the records if all Region numbers have not been selected, but no records are returned. Also, I have tried a IIf on the Or line under Region Number to the effect that if all the regions are "", then 1 and 2 and 3 and 4 and 5 and 6 and 7 and 8,[RegionNumber]. I have tried a two pronged query, the first as shown above, and then a query based on that query to add the IIf region numbers are "", then 1 and 2 and etc.

    So if anyone is feeling particularly bored and wish to help me out I would be so very thankful as this is really driving me nuts!

    Thank you for taking the time to read this!

    Bill
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't use dynamic parameterized queries.

    I would use VBA code to conditionally build criteria string and apply to form filter property. Review http://www.allenbrowne.com/ser-62code.html

    or pass the criteria to form or report when they open:

    DoCmd.OpenReport "reportname", , , strFilter
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Using a case structure and put your radio buttons in a group can help simplify it using VBA

    you can also build a string to put into the SQL statement

    select case radiobuttons
    case 1
    strWhere = 1
    case 2
    strWhere = 2

    and so on

    this way it shortens everything and you have an easy string variable stuffed with the right number inside

    make another case statement for the status too and stuff that into a different string (or append it to the strWhere)

  4. #4
    Oblio is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    50
    Thank you for your suggestions...I am really not acquainted with writing code, but will try to see if I can figure it out from the Allen Browne link which looks very thorough. Thanks again for taking the time to help me!

    Have a great weekend!

  5. #5
    Oblio is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    50
    Thanks very much for trying to help me...I am not very acquainted with writing code...but will certainly try to understand what you have suggested...at least I know now it is not something easily done in a SQL query.

    Hope you enjoy a great weekend! and thanks again for trying to help me out!

  6. #6
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by Oblio View Post
    Thanks very much for trying to help me...I am not very acquainted with writing code...but will certainly try to understand what you have suggested...at least I know now it is not something easily done in a SQL query.

    Hope you enjoy a great weekend! and thanks again for trying to help me out!
    Ok just let us know what part you can't understand and we'll help out.

  7. #7
    Oblio is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    50

    Multiple region selections possible

    Quote Originally Posted by Ruegen View Post
    Ok just let us know what part you can't understand and we'll help out.
    Thank you for the offer to help!!

    I am very new to this so please forgive my ignorance...I am trying to figure this out, however, I am confused about the Case Select code and the radio button grouping.

    So first, I know radio buttons are used most often as an either or response...so using them was a mistake I think and I should use check boxes instead as ANY combination of Region Numbers can be selected...is this right?

    Does this still mean I should use a Select Case Code solution?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you want to allow multiple checkboxes to be selected then each must be bound to a separate field and not within an OptionGroup and no code. However, this appears not to be a normalized data structure and will likely cause you frustrations in the future.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Oblio is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    50

    Too Bad

    Quote Originally Posted by June7 View Post
    If you want to allow multiple checkboxes to be selected then each must be bound to a separate field and not within an OptionGroup and no code. However, this appears not to be a normalized data structure and will likely cause you frustrations in the future.
    I was able to get the Qualifications combobox to allow a Null response to return ALL possible choices, in addition to the ability for it to simply pass the single value selected by the combobox instead. I got the idea from a post involving Ken Sheridan and someone named Ed...

    HTML Code:
    http://msgroups.net/microsoft.public.access.gettingstarted/pass-field-value-selec/99189
    Here is the QualificationsID final IIf statement which allows for a selection from the combobox And will also return all records if the combobox value is Null:

    Code:
    Like IIf([Forms]![f_Choose_Regions]![cboQualifications] Is Null,"*",[Forms]![f_Choose_Regions]![cboQualifications]) Or Is Null
    So all is not lost as the user can choose any or all regions, it just means more clicking for them. Here is the link to the database if anyone wishes to look at it:
    HTML Code:
    http://1drv.ms/Yvjmkg
    .

    Here is the SQL from the query based on the parameter form f_Choose_Regions:

    Code:
    SELECT lt_Region.RegionNumber, lt_Status.StatusName, t_FSESO.QualificationsID, t_FSESO.InstructorID, t_FSESO.RegionID, t_FSESO.FName, t_FSESO.LName, t_FSESO.StatusID, lt_Qualifications.QualificationsDescFROM lt_Status INNER JOIN (lt_Region INNER JOIN (lt_Qualifications INNER JOIN t_FSESO ON lt_Qualifications.QualificationsID = t_FSESO.QualificationsID) ON lt_Region.RegionID = t_FSESO.RegionID) ON lt_Status.StatusID = t_FSESO.StatusID
    WHERE (((lt_Region.RegionNumber) Like IIf([Forms]![f_Choose_Regions]![Reg1]=-1,1,"") Or (lt_Region.RegionNumber) Like IIf([Forms]![f_Choose_Regions]![Reg2]=-1,2,"") Or (lt_Region.RegionNumber) Like IIf([Forms]![f_Choose_Regions]![Reg3]=-1,3,"") Or (lt_Region.RegionNumber) Like IIf([Forms]![f_Choose_Regions]![Reg4]=-1,4,"") Or (lt_Region.RegionNumber) Like IIf([Forms]![f_Choose_Regions]![Reg5]=-1,5,"") Or (lt_Region.RegionNumber) Like IIf([Forms]![f_Choose_Regions]![Reg6]=-1,6,"") Or (lt_Region.RegionNumber) Like IIf([Forms]![f_Choose_Regions]![Reg7]=-1,7,"") Or (lt_Region.RegionNumber) Like IIf([Forms]![f_Choose_Regions]![Reg8]=-1,8,"")) AND ((lt_Status.StatusName) Like IIf([Forms]![f_Choose_Regions]![ACTIVE]=-1,"ACTIVE","") Or (lt_Status.StatusName) Like IIf([Forms]![f_Choose_Regions]![INTERIM]=-1,"INTERIM","") Or (lt_Status.StatusName) Like IIf([Forms]![f_Choose_Regions]![PROBATION]=-1,"PROBATION","") Or (lt_Status.StatusName) Like IIf([Forms]![f_Choose_Regions]![ON LEAVE]=-1,"ON LEAVE","") Or (lt_Status.StatusName) Like IIf([Forms]![f_Choose_Regions]![SUSPENDED]=-1,"SUSPENDED","") Or (lt_Status.StatusName) Like IIf([Forms]![f_Choose_Regions]![RESIGNED]=-1,"RESIGNED","") Or (lt_Status.StatusName) Like IIf([Forms]![f_Choose_Regions]![DE-CERTIFIED]=-1,"DE-CERTIFIED","") Or (lt_Status.StatusName) Like IIf([Forms]![f_Choose_Regions]![DECEASED]=-1,"DECEASED","")) AND ((t_FSESO.QualificationsID) Like IIf([Forms]![f_Choose_Regions]![cboQualifications] Is Null,"*",[Forms]![f_Choose_Regions]![cboQualifications]) Or (t_FSESO.QualificationsID) Is Null));
    
    Hope this helps someone as I have been weeks working on this...so thanks so much for steering me in the right direction!
    Bill

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Correction to my earlier post. I was thinking of data entry. So if purpose of controls are for selecting filter criteria, then should not be bound. Checkboxes in OptionGroup was not appropriate.

    Glad you found a solution.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 10-26-2013, 02:04 PM
  2. Replies: 1
    Last Post: 06-06-2013, 06:12 PM
  3. Replies: 5
    Last Post: 03-22-2013, 01:11 PM
  4. Replies: 0
    Last Post: 03-06-2011, 04:10 AM
  5. Replies: 5
    Last Post: 10-08-2009, 05:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums