Recordset not returning records

[TinaCa]

I am trying to get two fields from a table so I can update one of them based on the other.

Set db = CurrentDb
strSQL = "SELECT tblInvoiceDetails.companyid, tblInvoiceDetails.invoiceid " _
& "FROM tblInvoiceDetails WHERE [invoiceid] is null "

Set rs = db.OpenRecordset(strSQL)

CustCnt = rs.RecordCount



When I check CustCnt to see it worked it CustCnt = 1 but my companyid field is null i.e. no records were returned.

any help much appreciated.

[Jeanette Cunningham]

If you want records where the companyid is null, try this:
Set db = CurrentDb
strSQL = "SELECT tblInvoiceDetails.companyid, tblInvoiceDetails.invoiceid " _
& "FROM tblInvoiceDetails WHERE [companyid] is null "

Set rs = db.OpenRecordset(strSQL)

CustCnt = rs.RecordCount

[NoellaG]

Hi,

when you're working with recordsets you always have to move to the last record before you can do the count. You can check on empty recordsets by checking if .BOF (begin of file) and .EOF (end of file) are both true at the same time.

grNG

[TinaCa]

Noella, that got me the correct count - Thanks. I am still getting empty for the CompanyId field I need to use this field to do my loop. Is my code for getting the value of the companyId incorrect?

CustId = CompanyId

Thanks.