Results 1 to 4 of 4
  1. #1
    Pard is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2021
    Posts
    1

    DAO recorrdset is NOT returning Record Count


    Why does this return a record count of zero when there are records in the recordset (or at least, I believe there should be)?

    Code:
    Sub ChkForRecordsB4DelCat()
    
    
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim dblRcrdCount As Double
    
    
    strSQL = "SELECT * FROM tbl_Inv_Donations WHERE [Inv_Cat_No] = " & Me.tx_Cat_Mstr_No.Value & " AND [Inv_Del_Date] Is Null"
    
    Set db = CurrentDb()
    Set rst = db.OpenRecordset(strSQL)
    
    If rst.EOF Then
    dblRcrdCount = 0
    Else
    rst.MoveLast
    rst.MoveFirst
    dblRcrdCount = rst.RecordCount
    Debug.Print dblRcrdCount
    End If
    
    rst.Close
    db.Close
    
    
    End Sub

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Test the sql on it's own.
    I hope the cat field is numeric?
    Could also walk through the code? I know that is not fashionable these days, but is what I would also do.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    WGM, if that is for the old fashioned, count me in! One way I know you and I advocate troubleshooting this exact scenario is to debug.print the sql. If it looks good, then copy it to a new query in sql view and run it. If it doesn't balk but returns no records, it is your criteria or joins, if any. If it balks, the offending part is usually highlighted.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Does a query object using the same criteria return records?
    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.

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

Similar Threads

  1. Returning 0 value if count() is null
    By scott0_1 in forum Queries
    Replies: 5
    Last Post: 05-14-2018, 06:37 PM
  2. Returning Difference of Count in Same Table
    By jmltinc in forum Queries
    Replies: 5
    Last Post: 12-21-2016, 01:01 PM
  3. Returning a specific count on a web form
    By Bobwords in forum Forms
    Replies: 9
    Last Post: 07-07-2014, 12:58 PM
  4. Count is returning the wrong count.
    By khughes46 in forum Reports
    Replies: 2
    Last Post: 05-26-2014, 12:46 PM
  5. Replies: 6
    Last Post: 04-26-2012, 10:00 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