Results 1 to 9 of 9
  1. #1
    cardgage is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2011
    Posts
    50

    DAO OpenRecordset method returning RecordCount of 0 in VBA

    Good morning,

    I am trying to use VBA to dynamically return the number of tasks that were completed between two dates that the user types into two separate text boxes (txtStartDate and txtEndDate). I built a query that works using the following SQL statement:

    Code:
    SELECT tblTasks.StatusTypeID, tblTasks.DateCompleted FROM tblTasks WHERE (((tblTasks.StatusTypeID)=6) And ((tblTasks.DateCompleted)>=[StartDate] And (tblTasks.DateCompleted)<=[EndDate]));
    However, I am trying to build a form that acts as a sort of dashboard where the user can select different status types (eg, completed, awaiting review, etc...) without having to write a bunch of queries. Therefore, I thought trying to dynamically set the SQL statement via VBA code would be useful. I have the following code that runs when I click a command button, but this consistently returns 0 results regardless of the dates that are inputted into txtStartDate and txtEndDate. Any ideas on what I am doing wrong?

    Code:
    Option Compare Database
    
    Private Sub cmdRunReport_Click()
    
    
      Dim StartDate, EndDate As String
      Dim TaskSQL As String
      Dim CompleteTasks As Integer
      
      StartDate = Me.txtStartDate.Value
      EndDate = Me.txtEndDate.Value
      
      TaskSQL = "SELECT tblTasks.StatusTypeID, tblTasks.DateCompleted " _
        & "FROM tblTasks " _
        & "WHERE (((tblTasks.StatusTypeID)=6) AND " _
        & "((tblTasks.[DateCompleted])>=" & StartDate & " AND " _
        & "(tblTasks.[DateCompleted])<=" & EndDate & "));"
        
      CompleteTasks = CompletedTasksInTimeframe(TaskSQL)
      
      MsgBox "You completed " & CompleteTasks & " tasks between " _
        & StartDate & " and " & EndDate & ".", vbInformation, "Task count"
    
    
    End Sub
    
    
    Private Function CompletedTasksInTimeframe(valSQL As String) As Integer
    
    
      Dim db As DAO.Database
      Dim rs As DAO.Recordset
      
      On Error GoTo Error_Handler
      
      Set db = CurrentDb
      Set rs = db.OpenRecordset(valSQL)
      
      CompletedTasksInTimeframe = rs.RecordCount
      
    Exit_Handler:
        On Error Resume Next
        rs.Close
        db.Close
        Set rs = Nothing
        Set db = Nothing
        Exit Function
     
    Error_Handler:
        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
               "Error Number: " & err.Number & vbCrLf & _
               "Error Source: CreateAppointment" & vbCrLf & _
               "Error Description: " & err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl), _
               vbOKOnly + vbCritical, "An error has occurred!"
        Resume Exit_Handler
    
    
    End Function
    Thank you in advance for any help!
    cardgage

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Couple of things...

    Dim StartDate as String, EndDate As String


    Also need delimiters for dates..

    TaskSQL = "SELECT tblTasks.StatusTypeID, tblTasks.DateCompleted " _
    & "FROM tblTasks " _
    & "WHERE (((tblTasks.StatusTypeID)=6) AND " _
    & "((tblTasks.[DateCompleted])>=#" & StartDate & "# AND " _
    & "(tblTasks.[DateCompleted])<=#" & EndDate & "#));"

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Try to dim your variables as dates, not strings (Dim StartDate as Date, EndDate as Date).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    cardgage is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2011
    Posts
    50
    Thank you very much davegri, the code is working perfectly now! I always feel silly when it's something simple like missing #'s.

    Quote Originally Posted by davegri View Post
    Dim StartDate as String, EndDate As String
    This is interesting to me. I have been under the impression that one could define any number of variables of the same type on the same line without having to specify each one. Is this specific to String variables, or is this true for all variable types?

    Quote Originally Posted by Gicu View Post
    Try to dim your variables as dates, not strings (Dim StartDate as Date, EndDate as Date).
    I had tried defining these variables as both Dates and Strings before posting, but was still getting a RecordCount of 0 before making the change that davegri suggested.

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    This is interesting to me. I have been under the impression that one could define any number of variables of the same type on the same line without having to specify each one. Is this specific to String variables, or is this true for all variable types?
    All of 'em.

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    In response to post 5 and 6, maybe not. Here's some data.

    Code:
    Public Sub TestDim1()
        Dim s1, s2 As String
        Dim s3, s4 As Long
        Dim s5, s6 As Date
        Debug.Print "s1 " & VarType(s1), TypeName(s1)
        Debug.Print "s2 " & VarType(s2), TypeName(s2)
        Debug.Print "s3 " & VarType(s3), TypeName(s3)
        Debug.Print "s4 " & VarType(s4), TypeName(s4)
        Debug.Print "s5 " & VarType(s5), TypeName(s5)
        Debug.Print "s6 " & VarType(s6), TypeName(s6)
    End Sub
    
    call testdim1
    s1 0          Empty
    s2 8          String
    s3 0          Empty
    s4 3          Long
    s5 0          Empty
    s6 7          Date
    Above, when s1, s3 and s5 are not explicitly typed, the results show empty, or not defined.

    Code:
    Public Sub TestDim2()
        Dim s1, s2 As String
        Dim s3, s4 As Long
        Dim s5, s6 As Date
        s1 = "Hello"
        s3 = 100000000.222222
        s5 = CDate("1/1/2020")
        Debug.Print "s1 " & VarType(s1), TypeName(s1)
        Debug.Print "s2 " & VarType(s2), TypeName(s2)
        Debug.Print "s3 " & VarType(s3), TypeName(s3)
        Debug.Print "s4 " & VarType(s4), TypeName(s4)
        Debug.Print "s5 " & VarType(s5), TypeName(s5)
        Debug.Print "s6 " & VarType(s6), TypeName(s6)
    End Sub
    
    call testdim2
    s1 8          String
    s2 8          String
    s3 5          Double
    s4 3          Long
    s5 7          Date
    s6 7          Date
    However, when untyped variables are assigned a value, they take on the type of the value.
    So the conclusion is that the types are implicitly assigned when the variable is given a value. This is probably OK in most cases.
    The old mantra that variables without a declared type are automatically declared as variants appears to be false. Live and learn.

  8. #8
    cardgage is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2011
    Posts
    50
    This is exceedingly interesting, I ran the following for my own validation to see what happened if I mixed up the order of what is assigned to s1, s3 and s5:

    Code:
    Public Sub TestDim()
        Dim s1, s2 As String
        Dim s3, s4 As Long
        Dim s5, s6 As Date
        s1 = 100000000.222222
        s3 = CDate("1/1/2020")
        s5 = "Hello"
        Debug.Print "s1 " & VarType(s1), TypeName(s1)
        Debug.Print "s2 " & VarType(s2), TypeName(s2)
        Debug.Print "s3 " & VarType(s3), TypeName(s3)
        Debug.Print "s4 " & VarType(s4), TypeName(s4)
        Debug.Print "s5 " & VarType(s5), TypeName(s5)
        Debug.Print "s6 " & VarType(s6), TypeName(s6)
    End Sub
    
    Call TestDim
    s1 5          Double
    s2 8          String
    s3 7          Date
    s4 3          Long
    s5 8          String
    s6 7          Date
    Needless to say, I went through my database and specified variable types for everything. Better safe than sorry!

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Needless to say, I went through my database and specified variable types for everything. Better safe than sorry!
    Hear, Hear!

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

Similar Threads

  1. OpenRecordset method
    By Kundan in forum Programming
    Replies: 4
    Last Post: 03-13-2018, 09:33 PM
  2. Replies: 3
    Last Post: 07-26-2017, 08:14 AM
  3. Global variable for recordcount
    By Larryg in forum Forms
    Replies: 4
    Last Post: 03-19-2017, 04:31 PM
  4. checking date when recordcount > 0
    By JeroenMioch in forum Programming
    Replies: 14
    Last Post: 03-05-2013, 12:39 PM
  5. Recordcount with filter on
    By injanib in forum Forms
    Replies: 3
    Last Post: 06-15-2011, 03:07 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