Solved

Get record count using SQL statement in Access

Posted on 2008-09-30
6
985 Views
Last Modified: 2013-11-28
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

0
Comment
Question by:BobRosas
  • 3
  • 3
6 Comments
 
LVL 11

Expert Comment

by:aaronakin
ID: 22607625
Try giving the counted value an alias.  i.e. "SELECT Count(tblDaily.QCReportNo) AS QCReportNoCount "

Then, try assigning dblCount = QCReportNoCount.
0
 

Author Comment

by:BobRosas
ID: 22607718
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
 

Author Comment

by:BobRosas
ID: 22607828
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 11

Expert Comment

by:aaronakin
ID: 22607844
It's a while since I last used Access.  Check out this article though and see if it helps you.
0
 
LVL 11

Accepted Solution

by:
aaronakin earned 125 total points
ID: 22607845
0
 

Author Closing Comment

by:BobRosas
ID: 31501634
Yes that helped.  I got it working
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now