checking if recordset is empty

[mp3909]

I use the following piece of code to check if my recordset is empty:

If rs.RecordCount > 0 Then
    MissingLineIds = True
Else
    MissingLineIds = False
End If

But the problem is that even though my recordset should theoretically be empty, it somehow satisfies the first if condition (i.e. rs.RecordCount > 0).


And when I do debug.print rs.fields(0) it prints 0.

So I am beginning to wonder that if you want to check if a recordset is empty it should be recordcount > 1 ??

[Micron]

"debug.print rs.fields(0) it prints 0". Then in the table, that field is a number field with a default of 0 and there is in fact a record? Because you are examining the value in a field with that statement. If the output is "0" then you have that as a value in that field somewhere. Examine all the fields in the recordset or run the query it's based on and check that. Also possible that you allow zero length ("") in a field and have a record with one or more fields with zls as a value. You can always run a query on those fields where myField = "" and see how many records you get.
The logical conclusion is that you have at least one record, regardless of what you think you're seeing.

[mp3909]

I'm seeing this when I replicate what my SQL query is trying to do:



Just a blank record.

[pbaldy]

I agree that if the debug is printing 0, there is a record being returned. My test would be:

If Not rs.EOF Then

[Micron]

remove the filters and see if anything changes.

[mp3909]

no I need the filters there.

[mp3909]

silly me, my sql query is a count!!
even if there are no records, the result will be a count of 0, right?

[thebigthing313]

Would you mind sharing the line of code where you set the recordset object?

EDIT: It depends on how the SQL statement is written. However you have it written now, a record is being returned with 0 being the value of the first column of the first record.

[Micron]

even if there are no records, the result will be a count of 0, right?

Ja, your result is a calculation, hence query returns 1 record. If you really need to know when there's no actual data returned, you'd have to set the comparison to > 1 but I think it would be best to alter the query to return only where some count is greater than 0 if possible.