Results 1 to 6 of 6
  1. #1
    bds022 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    8

    Syntax to deal with empty combo box when used as a query input parameter

    I have a user interface that allows a user to interact with a combo box to refine search criteria and the records that are returned (in a subform) from a query. I do not want an empty combo box to mean that the query only returns records with a corresponding empty value. Instead, I want all values returned when the combo box is empty (the user just has not yet interacted with the search menu at that level of detail).



    The basic idea I am trying to accomplish is here (put on the criteria line in the query design view):

    IIf(IsNull([Forms]![PlanBuilder]![cmbABETOutcomeSearch]),Like "#" Or Is Null,[Forms]![PlanBuilder]![cmbABETOutcomeSearch])

    If the combo box has a null value, I want it to return records with any number in that field, including those that have a null value in the field. If the user has specified a value in the combo box, then I want the query to return only records with that value in the field.

    The individual components work great. My line

    Like "#" Or Is Null

    does what I want when entered into the criteria line directly. But I get a syntax error when I embed within iff as I have done above. Any idea how to clear the syntax problem?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you would use something like

    WHERE (sometablefield like [Forms]![PlanBuilder]![cmbABETOutcomeSearch] or [Forms]![PlanBuilder]![cmbABETOutcomeSearch] IS NULL)


    note # is a wildcard - it means any number digit



  3. #3
    bds022 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    8
    Thanks, but I think what you describe would behave the same whether the user has interacted with the combo box, or not. I need it to behave differently. If the combo box has not been used or is otherwise empty, I don't want it to affect the query. But once it has a value, I do want its value included as query criteria.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you try suggestion? I did. It seems every record is returned whether or not parameter is provided.

    I don't use dynamic parameters in query. I prefer VBA to build criteria and apply to form or report http://allenbrowne.com/ser-62.html
    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
    bds022 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    8
    I would prefer to set this up as a dynamic parameter in the query design window. I am a fairly novice user myself, and my team that will be using this knows even less about Access and VBA

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    suggest provide some example data, an example of something selected in the combobox and the expected result

    as already stated, # is a wildcard

    but I think what you describe would behave the same whether the user has interacted with the combo box, or not.
    that's what your think? did you try it?

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

Similar Threads

  1. Replies: 5
    Last Post: 03-21-2018, 01:07 PM
  2. Replies: 3
    Last Post: 07-06-2016, 09:37 PM
  3. Query syntax to fill a field if empty
    By louise in forum Queries
    Replies: 4
    Last Post: 07-21-2015, 07:26 AM
  4. Replies: 3
    Last Post: 03-06-2013, 03:52 PM
  5. Replies: 1
    Last Post: 07-14-2008, 12:15 PM

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