Get record count using SQL statement in Access

I would like to find the 'count' of the field 'QCReportNo' and save it in a variable.
I've attached my code but can't even get it to compile.  I get the error...
    Wrong number of arguments or invalid property assignment.
strSQL = "SELECT Count(tblDaily.QCReportNo) "
strSQL = strSQL & "FROM tblDaily INNER JOIN (tblInstrumentsUsed INNER JOIN tblInstrumentsDesc ON tblInstrumentsUsed.InstrumentDescId = tblInstrumentsDesc.InstrumentDescId) ON tblDaily.QCReportNo = tblInstrumentsUsed.QCReportNo "
strSQL = strSQL & "WHERE (((tblDaily.QCReportNo)=[Forms]![frmPrevDailyDFT]![cboQCRptNo]))"
DoCmd.RunSQL (strSQL)
dblCount = Count(QCReportNo)

Open in new window

BobRosasAsked:
Who is Participating?
 
aaronakinCommented:
Try giving the counted value an alias.  i.e. "SELECT Count(tblDaily.QCReportNo) AS QCReportNoCount "

Then, try assigning dblCount = QCReportNoCount.
0
 
BobRosasAuthor Commented:
Thank you so much for your quick response.  I've changed my code and reposted.  Now I get the message 'Variable not defined" for the alias value.  However it could be related to my statement.
I tried running the code with the last line commented out and it compiles but on the
Do.Cmd.RunSQL (strSQL)
I get the error...
A RunSQL action requires an argument consisting of an SQL statement.
If I just want a count do I even need the do.cmd line?
Thanks in advance.
    strSQL = "SELECT Count(tblDaily.QCReportNo) As RptNoCount "
    strSQL = strSQL & "FROM tblDaily INNER JOIN (tblInstrumentsUsed INNER JOIN tblInstrumentsDesc ON tblInstrumentsUsed.InstrumentDescId = tblInstrumentsDesc.InstrumentDescId) ON tblDaily.QCReportNo = tblInstrumentsUsed.QCReportNo "
    strSQL = strSQL & "WHERE (((tblDaily.QCReportNo)=[Forms]![frmPrevDailyDFT]![cboQCRptNo]))"
    DoCmd.RunSQL (strSQL)
    dblCount = RptNoCount  

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
BobRosasAuthor Commented:
I changed my SQL code and added a 'group by' because I think it needs it to give me the right count.  But the errors are the same as above.

strSQL = "SELECT Count(tblDaily.QCReportNo) AS RptNoCnt "
     strSQL = strSQL & "FROM tblDaily INNER JOIN (tblInstrumentsUsed INNER JOIN tblInstrumentsDesc ON tblInstrumentsUsed.InstrumentDescId = tblInstrumentsDesc.InstrumentDescId) ON tblDaily.QCReportNo = tblInstrumentsUsed.QCReportNo "
     strSQL = strSQL & "GROUP BY tblDaily.QCReportNo "
     strSQL = strSQL & "HAVING (((tblDaily.QCReportNo)=[Forms]![frmPrevDailyDFT]![cboQCRptNo]))"

Open in new window

0
 
aaronakinCommented:
It's a while since I last used Access.  Check out this article though and see if it helps you.
0
 
BobRosasAuthor Commented:
Yes that helped.  I got it working
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.