Invalid Operation on a querydef

[DontKnow]

Hi Guys,

I am having some problems finding out a value in a count for a field in a
query.

Below is the code that I am using and I am getting an error on the last line:

The reported error is "Invalid Operation"

howmany = qdf.Fields("noof")

I don't know what I am doing wrong??

Can someone help me please??

Cheers
**************************************

Dim dbs As Database

Dim strsql As String
Dim count As Single
Dim qdf As QueryDef
Dim todaydate As Date
Dim howmany As Single
Set dbs = DBEngine(0)(0)

todaydate = Format(Date, "dd-mmm-yy")
strsql = "SELECT Count(Exam.UserID) AS noof " & _
"FROM Exam " & _
"GROUP BY Exam.date " & _
"Having (((Exam.date)= #" & Format(todaydate, "dd-mmm-yy") & "#));"



Set qdf = CurrentDb.CreateQueryDef("", strsql)

qdf.OpenRecordset
howmany = qdf.Fields("noof") ' error here
End Function

 

[John W. Vinson]

Hi Guys,

I am having some problems finding out a value in a count for a field in a
query.

Below is the code that I am using and I am getting an error on the last line:

The reported error is "Invalid Operation"

howmany = qdf.Fields("noof")

I don't know what I am doing wrong??

If you're looking at a field's value, you need a Recordset object, not a
Querydef object:

Dim rs As DAO.Recordset
Set qdf = CurrentDb.CreateQueryDef("", strsql)

Set rs = qdf.OpenRecordset
howmany = rs.Fields("noof")
End Function

or much more simply, just use DCount() on the table.

 

[DontKnow]

Many thanks John!!
Works beautifully!!

Cheers

If you're looking at a field's value, you need a Recordset object, not a
Querydef object:

Dim rs As DAO.Recordset
Set qdf = CurrentDb.CreateQueryDef("", strsql)

Set rs = qdf.OpenRecordset
howmany = rs.Fields("noof")
End Function

or much more simply, just use DCount() on the table.