DAO Recordset always returns 1 when there are no records... (1 Viewer)

[cosmarchy]

I have the following code which performs a select query on the local database:

Sub Number()

    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Dim SQL_SELECT As String
            
    Set db = CurrentDb
            
    SQL_SELECT = "SELECT Max(tblP.PN) AS PN FROM tblP WHERE (((tblP.PN) Like '1ABC*'));"

    Set rs = db.OpenRecordset(SQL_SELECT)
    
    If rs.EOF Then
        Debug.Print "0"
    Else
        rs.MoveFirst
        rs.MoveLast
        Debug.Print rs.RecordCount
    End If

End Sub

The trouble is that there are no records in the table which match the WHERE criteria but the RecordCount always returns 1 for some reason.

Strange thing is that if I run the SQL code in Query Designer, it doesn't return any records which is what you would expect the RecordCount to reflect in the VBA code...

Anyone have any ideas? Thanks

 

[Gasman]

If there no records, then the eof path would be taken?
If not eof, then some record(s) must be there?

 

[MajP]

That query will always return 1 record regardless of the criteria.

 

[cosmarchy]

@MajP - is that because of Max???

How would you get the latest record in a series? What is the workaround?

 

[The_Doc_Man]

First problem, define "last" - and be warned, that's a trick question.

The real answer is that you cannot determine "Max" of anything until you know that anything exists to take "Max" of. Do your rs.Count test on a recordset that doesn't contain any SQL aggregates. Or do this:

If DCount("[PN]", "tblP", "[PN] LIKE '1ABC*'" ) = 0 Then
'    return count of 0
Else
'    open the recordset to determine the count.
End If

 

[theDBguy]

The trouble is that there are no records in the table which match the WHERE criteria but the RecordCount always returns 1 for some reason.

I am curious how you tested that, since the Debug.Print rs.RecordCount is inside the Else clause, which would be skipped if the recordset was empty (rs.EOF = True).

 

[The_Doc_Man]

The record count is 1 because an aggregate query always returns 1 answer - the aggregated value. Even for the case where the unaggregated record count is 0, there is the totals line - which will report a total of 0. You aren't looking at the recordset you THINK you are examining.

 

[MajP]

@MajP - is that because of Max???

How would you get the latest record in a series? What is the workaround?

"SELECT Top1 PN FROM tblP WHERE tblP.PN Like '1ABC*' Order BY PrimaryKeyIfAutoNumber Desc"

If you do not have an autonumber as your PK or an incrementing value then you need a timestamp. Regardless you need to be able to have a field to sort by.

 

[arnelgp]

If rs.EOF Then

you test Both Bof and Eof:

If rs.BOF and rs.EOF Then 'there is no record

 

[Solo712]

First problem, define "last" - and be warned, that's a trick question.

The real answer is that you cannot determine "Max" of anything until you know that anything exists to take "Max" of. Do your rs.Count test on a recordset that doesn't contain any SQL aggregates. Or do this:

If DCount("[PN]", "tblP", "[PN] LIKE '1ABC*'" ) = 0 Then
'    return count of 0
Else
'    open the recordset to determine the count.
End If

Or simply

RecordCount = DCount("*", "tblP", "[PN] LIKE '1ABC*'" )

 

[cosmarchy]

"SELECT Top1 PN FROM tblP WHERE tblP.PN Like '1ABC*' Order BY PrimaryKeyIfAutoNumber Desc"

If you do not have an autonumber as your PK or an incrementing value then you need a timestamp. Regardless you need to be able to have a field to sort by.

Cheers, thanks for this, that seems to have done the trick...

For reference, I know that RecordCount is not returning correctly as I set breakpoints at various points and was able to pickup the variable values as I walked through the code...

 

[MajP]

For reference, I know that RecordCount is not returning correctly as I set breakpoints at various points and was able to pickup the variable values as I walked through the code...

I am pretty sure it is correct. You do understand that recordset Always returns at least 1 record? It will never be 0.

 

[Gasman]

I am pretty sure it is correct. You do understand that recordset Always returns at least 1 record? It will never be 0.

@MajP Are we just talking about an aggregate recordset?

 

[The_Doc_Man]

See my post #7. A single-field aggregate query that doesn't trigger a run-time error will always return 1 record. So SUM and COUNT always return one record even for a zero-input recordset. The SUM and/or the COUNT will be 0. Note that MIN, MAX, FIRST, and LAST might indeed return errors (no current record?). Haven't tried the more advanced aggregates before (i.e. AVG) and don't care to experiment at the moment, but they would probably return either a 0 record OR a "no current record" error.

 

[MajP]

@MajP Are we just talking about an aggregate recordset?

Test yourself. Build an aggregate with a criteria "where true= false" or something similar as the OP did where no records like "*1abc*"