How to compare to an empty recordset

[Twanne]

Hi,

I'm trying to get some values from one table to an other. Now I select all dates from one table and I search with them in another table. When there is a record found for that date it should perform a query. But when there is nog record foud for that date is return empty. How do I compare to that??

My code for now:

Set rst = db.OpenRecordset("SELECT DISTINCT Datum FROM Longfunctie;")
    Do Until rst.EOF
        Set rst2 = db.OpenRecordset("SELECT eadnr, parameter, Waarde FROM PatParameters WHERE Datum = " & rst.Fields(0).Value & ";")
        If (IsEmpty(rst2.Fields(0).Value)) Then
            Debug.Print rst.Fields(0) & " " & rst2.Fields(0).Value & " " & rst2.Fields(1).Value & " " & rst2.Fields(2).Value
        End If
        rst.MoveNext
    Loop
 

[barry07]

Although you say empty Recordset I think you mean empty Record in which case I would use the Nz function which returns a specified value when the original value is null. It's one of the peculiarities of Access that I have discovered that nulls aren't always matched by expressions such as "isNull" or ="", or even IsEmpty.

This, however is a surefire method of detecting a null:

If Nz(rst2.Fields(0),"") = ""  Then

If you really mean empty Recordset then test for rst.RecordCount <1

Hope this helps.

[Twanne]

Hi,

I've tried tthat to, but it didn't work either. The problem is that the recordset is empty, not the record itself. So it is a recordset without records.

I've searched some more on other sites :p and found that it is possible to check the recordset itself with the .EOF and .BOF parameters. If they are both true than there is no record in the recordset. I've only found it after some hours of searching.

Thanx for the help anyway.

Greetz
Twanne

Living is hard, dying is easier.

[MMcCarthy]

You need to relook at your logic. If no matching date then there is no record to be found or printed. This should work to catch empty returns for the second recordset.

Set rst = db.OpenRecordset("SELECT DISTINCT Datum FROM Longfunctie;")
    Do Until rst.EOF
    Set rst2 = db.OpenRecordset("SELECT eadnr, parameter, Waarde FROM PatParameters WHERE Datum = " & rst.Fields(0).Value & ";")
    rst2.MoveLast
    rst2.MoveFirst
    If rst2.RecordCount = 0 Then
        ' No record found
    Else
        Debug.Print rst.Fields(0) & " " & rst2.Fields(0).Value & " " & rst2.Fields(1).Value & " " & rst2.Fields(2).Value
    End If
    rst.MoveNext
    Loop