Invalid Operation on a querydef

  • Thread starter Thread starter DontKnow
  • Start date Start date
D

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
 
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??
Click to expand...

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.
 
Many thanks John!!
Works beautifully!!

Cheers

John W. Vinson said:
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.
Click to expand...
 
Back
Top