Results 1 to 8 of 8
  1. #1
    TenOc is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Feb 2015
    Posts
    94

    Search Form with Option group with 3 option buttons

    Please excuse me if I do not use the correct terminology, I’m a newbie




    On my form I have an option group with 2 option buttons namely “Class A” and “Class B”. This form opens a query and later a report which allows me to select either class A or class B customers. I now want to add to this option group a third button namely, ”All” which will include both class A and class B customers in the report. The default values on the 2 option buttons are 1 and 2 respectively. The default value for the 3rd button I assume would be 3. In the customers table the stored value for Class is either 1 or 2, they are no number 3.


    The current selection criteria in the query is “[Forms]![frmSearch]![opgClass]” -- all works well with 2 buttons. I have attempted, (when I have 3 buttons) to use a “like” criteria with wildcards with no joy. I need help with the new selection criteria in the query with 3 buttons. I think I may need some type of IIF statement but I do not know how to structure it if the option class is 3.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,106
    LIKE IIf([Forms]![frmSearch]![opgClass] = 3, "*", [Forms]![frmSearch]![opgClass])
    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
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,238
    Surely if it is All, you just do not use any criteria?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,106
    My understanding is it's a dynamic parameter in query object. The parameter receives whatever is in option group. All values must be dealt with.
    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.

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,238
    Quote Originally Posted by June7 View Post
    My understanding is it's a dynamic parameter in query object. The parameter receives whatever is in option group. All values must be dealt with.
    Ah, I see now.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    1,108
    Personally I like to use a select case statement with option groups. Easier to make changes and easier to follow.

    Code:
    Select Case YourOptionGroupName
    
    Case 1
         strSql = "Select * ....... where SomeCriteria = Something
    
    Case 2
         strSql = "Select * ....... where SomeCriteria = Something
    
    Case 3
         strSql = "Select * ....... where SomeCriteria = Something
    
    Case else
         msgbox "something went wrong"
    
    End Select
    The current selection criteria in the query is “[Forms]![frmSearch]![opgClass]”
    Only use the option group values to indicate your choice. I would not use the OG values to associate to a value in your table ( like 1 for Class A, 2 for Class B)
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,106
    That VBA is not relevant to a dynamic parameterized query. However, need confirmation from OP if this is a DPQ.

    I have never used dynamic parameterized queries. I prefer VBA to build filter criteria and apply to form or report.
    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.

  8. #8
    TenOc is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Feb 2015
    Posts
    94
    Quote Originally Posted by June7 View Post
    LIKE IIf([Forms]![frmSearch]![opgClass] = 3, "*", [Forms]![frmSearch]![opgClass])
    Thanks this works. Problem solved

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

Similar Threads

  1. Replies: 14
    Last Post: 06-25-2020, 08:42 AM
  2. Option Group Hide buttons VBA codes
    By JennyL in forum Access
    Replies: 3
    Last Post: 01-13-2017, 05:12 PM
  3. Option group radio buttons
    By sharonir22 in forum Forms
    Replies: 3
    Last Post: 07-04-2015, 11:11 PM
  4. Use of option group buttons
    By sireesha in forum Forms
    Replies: 1
    Last Post: 10-25-2012, 01:20 PM
  5. option group radio buttons
    By ManC in forum Forms
    Replies: 9
    Last Post: 03-08-2010, 03:46 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