Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: Access 07: Query criteria: starting record

    Thanks for reading:
    I want to add a user defined criteria to this query def and not sure the most efficient method:
    should it be in the query named here, GoalsA? or
    as another line of criteria in the code below?

    I need an option in the query to show a range of accounts, or all accounts. I would preferably like the records to return a starting account (to the last account)
    I can choose account two ways:
    By field ACCT (Number) by putting in the beginning account number, or
    By field AcctName (Text) by putting in first letters of the accounts name

    either option would have to be " or blank for all"

    Note: the query returns records by account number, so I'm not sure I 'll get expected results by AcctName, unless I re-sort

    Code:
    Private Sub cmdOUT_Click()
    Dim DB As DAO.Database
    Dim QDF1 As QueryDef
    Dim where As Variant
    
    Set DB = CurrentDb()
    
    On Error Resume Next
    DB.QueryDefs.Delete ("GoalsA")
    On Error GoTo 0
    
    where = where & " AND (IsNull([Unapplied]))"
    where = where & " AND ([Salesrep1] Like '*" & [txtWCIGslmn] & "*' OR [SLMN2] Like '*" & [txtWCIGslmn] & "*' OR [SLMN3] Like '*" & [txtWCIGslmn] & "*')"
    where = where & " AND ([BAL1] Is Null OR [BAL1] > 0)"
    where = where & " AND ((([DUE]>Date())=0))"
    where = where & " AND (IsNull([ICID]))"
    
    
    Set QDF1 = DB.CreateQueryDef("GoalsA", "Select * From qryMainReport1" & (" where " & Mid(where, 6) & ";"))
    DoCmd.OpenReport "mrSO", acViewPreview
    
    
    End Sub
    Can anyone make suggestions to lead me in the right direction?
    THANKS!

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    I too do this user custom qry and what you have works.
    Note: There's no need to delete the query everytime...as long as it exists you can just rewrite over it every time...

    set qdf1 = querydefs("GoalsA")
    qdf1.SQL = "select * from qryMainRpt1 " & where

  3. #3
    Join Date
    Jun 2010
    Posts
    186
    Hi ranman256 - thanks....yes, the code works perfect - it's the criteria I want to add to it that I'm inquiring about...I want an option to select a customer range, or see all when I run it

    Quote Originally Posted by ranman256 View Post
    I too do this user custom qry and what you have works.
    Note: There's no need to delete the query everytime...as long as it exists you can just rewrite over it every time...

    set qdf1 = querydefs("GoalsA")
    qdf1.SQL = "select * from qryMainRpt1 " & where

  4. #4
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    On your form, you would have the option range boxes.
    Then when building your WHERE...
    Code:
    select case true
    case  isDate(txtDateStart) and not isDate(txtDateEnd) 
            'build range of only 1 date
    case  isDate(txtDateStart) and isDate(txtDateEnd) 
           'build range where
    end select

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The way you build the criteria (the WHERE part) is a bit strange and unnecessary complicated. Let's see, line by line what it yields:
    Code:
    Dim where As Variant
    1. At the beginning, where is empty (IsEmpty(where) = True). Why don't you use a String from the beginning?.
    Code:
    where = where & " AND (IsNull([Unapplied]))"
    2. IsNull([Unapplied]) yields either True or False --> where = "AND (True)" or where = "AND (False)".
    Code:
    where = where & " AND ([Salesrep1] Like '*" & [txtWCIGslmn] & "*' OR [SLMN2] Like '*" & [txtWCIGslmn] & "*' OR [SLMN3] Like '*" & [txtWCIGslmn] & "*')"
    3. OK.
    Code:
    where = where & " AND ([BAL1] Is Null OR [BAL1] > 0)"
    4. OK.
    Code:
    where = where & " AND ((([DUE]>Date())=0))"
    5. ([DUE]>Date())=0): why don't you write "([DUE]<=Date())" which is the same but clearer (if it's really what you intend to set as a condition)?
    Code:
    where = where & " AND (IsNull([ICID]))"
    6. Same as 2. IsNull([ICID]) yields either True or False --> where = "... AND (True)" or where = "... AND (False)".
    Code:
    Set QDF1 = DB.CreateQueryDef("GoalsA", "Select * From qryMainReport1" & (" where " & Mid(where, 6) & ";"))
    7. Why the parenthese at that place?

    Why dont you:

    a) Test each control and only add the corresponding part of the criteria when the value of the control is not Null?
    Code:
    If IsNull([Unapplied]) = False Then where = where & " AND ...
    b) Add a checkbox [ChkSelectAll] ("Select All") and test its value when building the query:
    Code:
    Private Sub BuildQuery()
    
        Dim strWhere As String
        Dim strSQL As String
    
        If Me.ChkSelectAll = True Then
            strWhere = ""
        Else
            strWhere = "1 = 1" ' So that strWhere will never begin with " AND..."
            ' correct the way you build the criteria, dont use it "as is".
            strWhere = strWhere & " AND (IsNull([Unapplied]))"
            strWhere = strWhere & " AND ([Salesrep1] Like '*" & [txtWCIGslmn] & "*' OR         [SLMN2] Like '*" & [txtWCIGslmn] & "*' OR [SLMN3] Like '*" & [txtWCIGslmn] & "*')"
            strWhere = strWhere & " AND ([BAL1] Is Null OR [BAL1] > 0)"
            strWhere = strWhere & " AND ((([DUE]>Date())=0))"
            strWhere = strWhere & " AND (IsNull([ICID]))" 
            where = " WHERE " & strWhere 
        End If
        strSQL = "Select * From qryMainReport1" & strWhere & ";"
    '    Debug.Print strSQL
        Set QDF1 = DB.CreateQueryDef("GoalsA", strSQL)
    
    End Sub
    That way, you can always examine the value of strSQL by uncommenting the "Debug.Print strSQL" line if anything goes wrong.
    Have a nice day!

Posting Permissions

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