asked on

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

aaronakin

Try giving the counted value an alias. i.e. "SELECT Count(tblDaily.QCReportNo) AS QCReportNoCount "

Then, try assigning dblCount = QCReportNoCount.

BobRosas

ASKER

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

BobRosas

ASKER

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

aaronakin

It's a while since I last used Access. Check out this article though and see if it helps you.

ASKER CERTIFIED SOLUTION

aaronakin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BobRosas

ASKER

Yes that helped. I got it working
Thanks