Results 1 to 2 of 2
  1. #1
    Join Date
    May 2017
    Posts
    1

    Unanswered: Show all records if text fields are null between 2 values

    How to show all records from a query when the values between two texboxes are null. I have the following.
    [QuoteID]>=[Forms]![frmMain]![txtQuoteFrom] and [QuoteID]<=[Forms]![frmMain]![txtQuoteTo]. This filters works correctly but I want to show all the records if both text fields are null. I am not sure to use
    iff not is null or iff is null

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    628
    Provided Answers: 32
    you must analyse the fields, then build the sql :
    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
    
    if IsNull(cboState) then
      me.filterOn = false
    else
           'remove 1st And
        sWhere= mid(sWhere,4)
    
         'just use the filter
    
      me.filter = sWhere
      me.filterOn = true
    endif
    
       'OR   
       'apply the sql to the form
    
    sSql = "SELECT * FROM tblCompany WHERE " & sWhere

Posting Permissions

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