Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2015
    Posts
    2

    Unanswered: Access Query Using IIf to Filter Results

    Hi guys, I'm kind of a newbie to access so I'm hoping someone can help me out with this.

    What I'm trying to accomplish is have a query that if a toggle button on a form is pressed it doesn't show the records with a certain field null, otherwise show every record.

    I've created a separate query adding "is not null" to the criteria of the field and it works just fine, but I would rather not use that for a solution, this db has 22 queries.

    In the field of the query I have
    Expr1: IIF( [Forms]![DataReturn]![toggleBtn] = true, Not isNull([Field]), [Field])

    Where Field is the field of interest.

    When the toggle is not pressed, I get all records. When the toggle is pressed, nothing is shown. I feel like I'm missing something conceptually, and any help would be greatly appreciated.

    Thanks in advance,
    Pat

  2. #2
    Join Date
    Jun 2015
    Posts
    2

    Access Query IIF to Filter Results

    Hi guys, I'm kind of a newbie to access so I'm hoping someone can help me out with this.

    What I'm trying to accomplish is have a query that if a toggle button on a form is pressed it doesn't show the records with a certain field null, otherwise show every record.

    I've created a separate query adding "is not null" to the criteria of the field and it works just fine, but I would rather not use that for a solution, this db has 22 queries.

    In the field of the query I have
    Expr1: IIF( [Forms]![DataReturn]![toggleBtn] = true, Not isNull([Field]), [Field])

    Where Field is the field of interest.

    When the toggle is not pressed, I get all records. When the toggle is pressed, nothing is shown. I feel like I'm missing something conceptually, and any help would be greatly appreciated.

    Thanks in advance,
    Pat

  3. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Like this?
    but for Toggle, it would be btnToggle.value
    me.filteron = btnToggle.value


    You cant use form boxes in a query if there's nothing in them..so..
    Test all controls for a possible filter then build the where clause.

    Code:
    if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
    if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
    if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value
    
        'remove 1st And
    sWhere= mid(sWhere,5)
    
      'just use the filter
    
    me.filter = sWhere
    me.filterOn = true
    
       'OR   
       'apply the sql to the form
    
    sSql = "SELECT * FROM tblCompany WHERE " & sWhere

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If it's ONLY null, non null or all records then consider a radio button group
    If it's null or all records then a check box may be the right route
    You can use a command button and set an appropriate caption.
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •