DAO.Recordset not returning any results

[sonny123]

Unfortunatley the table names have improper names with spaces in them but i have no controll over that

when i try to run this function in the immediate window I do not get the results I anticipate



The Table [Time Recording] has three entries which match the criteria passed

but im puzzled because not even the debug.print lines return anything at all which kind suggests that i havent opend the record set properly

the sqlStr looks right but nothing about has records or No records in the output form the immediate window ???

here is the code

Public Function duplicates(caseref As String, Code As Integer)
Dim ret As Integer
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Dim TableName As String
Dim CodeFieldA As String
Dim CodeFieldB As String
Dim CaseField As String
TableName = "[Time Recording]"
CodeFieldA = "[Activity Code]"
CodeFieldB = "[Ineligible Code]"
CaseField = "[Case Ref No]"
strSQL = "SELECT " & CaseField & " FROM " & TableName & " WHERE " & CaseField & " = """ & caseref & """ And (" & CodeFieldA & " = " & Code & " OR " & CodeFieldB & " = " & Code & ")"
Debug.Print strSQL
 
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbDenyWrite)
If (rs.RecordCount > 0) Then
    Debug.Print "Has Records"
    Else: Debug.Print "No Records"
End If
If rs.RecordCount <> 0 Then
    rs.MoveFirst
 
    While Not rs.EOF
        Debug.Print "count"
        rs.MoveNext
    Wend
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

and here is the output from the immediate window

?Duplicates("19378WB",909)
SELECT [Case Ref No] FROM [Time Recording] WHERE [Case Ref No] = "19378WB" And ([Activity Code] = 909 OR [Ineligible Code] = 909)

 

[pbaldy]

It doesn't error but it doesn't print any of the debug lines? Can you post the db here, or a representative sample?

 

[tehNellie]

You're testing for whether the recordset has data by using the .recordcount

To accurately report the .recordcount you need to go to the end of the recordset:
rs.movelast before you check the .recordcount

A better way of determining whether you have records present is to use the
.EOF (end of file) Property. If, after opening the recordset you are not rs.eof then there must be records present.


(if you try to use rs.movelast and there are no records, you'll get an error so you have to test for rs.eof before using rs.movelast anyway).

 

[ChrisO]

It works fine for me in Access2003

With three records in the table that match:-

?Duplicates("19378WB",909)
SELECT [Case Ref No] FROM [Time Recording] WHERE [Case Ref No] = "19378WB" And ([Activity Code] = 909 OR [Ineligible Code] = 909)
Has Records
count
count
count

 

[sonny123]

thanks I think it had something to do with the where clause
when i removed the Where clause i got results
perhaps its something to do with the quotes " " i tried no quotes and single quotes but to no avail

any way i just coded it differently without using a where clause and looped through all the records

 

[pbaldy]

Seems like it still should have triggered one of the other Debug lines, though your mixing of block and one-line formats might have confused the issue. As to better ways, what is it you're trying to accomplish? If all you want is a count, open a recordset on SQL like:

SELECT Count(*) AS HowMany FROM...WHERE...

and test the value of HowMany. You could also use a DCount() with the same criteria.