asked on

ACCESS VBA: Count Query of a SQL String

Experts:

I have a snipet of my VBA project below. The project runs fine, what I'm trying to do is add one more layer to it. What procedure would I use to assign a "query name" to the sql string below?

Ultimately I would like to run another query after this one executes which will return a single COUNT of the total number of records selected in the first query. I want to eventually pass this count to a txtbox in my project.

I've done this with the queries in ACCESS, though trying to do this programatically within VBA.

db.Execute "SELECT DISTINCT ths.ACCOUNTID, ioa.ASSETMAJORCLSS, ioa.MINALLOC, ioa.MAXALLOC, sum(val(ths.INSTCONCENTRATION)) " & _
"FROM  & tablename &  AS ths, tblIOAllocation AS ioa " & _
"WHERE ioa.INVSOBJECTIVE=ths.INVSOBJECTIVECD and ioa.ASSETMAJORCLSS=ths.ASSETCODE and ths.ASSETCODE='1' " & _
"GROUP BY ths.ACCOUNTID, ioa.ASSETMAJORCLSS, ioa.MINALLOC, ioa.MAXALLOC " & _
"HAVING sum(val(ths.INSTCONCENTRATION))>=ioa.minalloc and sum(val(ths.INSTCONCENTRATION))<=ioa.maxalloc "), dbFailOnError

Open in new window

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

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

Start Free Trial

donisanp

ASKER

rockiroads, thanks. I'm not trying to execute a predefined query, what I want to do is assign the SQL results as a query so that I can re-query it. The SQL I have listed is used for a couple of purposes, so I don't necessarily want to rewrite it.

What I want to do is query the results of the SQL string and port those results to a textbox on a form. For example, if I were to assign a query variable as:

qryVar = db.Execute "SELECT DISTINCT ths.ACCOUNTID, ioa.ASSETMAJORCLSS, ioa.MINALLOC, ioa.MAXALLOC, sum(val(ths.INSTCONCENTRATION)) " & _
"FROM & tablename & AS ths, tblIOAllocation AS ioa " & _
"WHERE ioa.INVSOBJECTIVE=ths.INVSOBJECTIVECD and ioa.ASSETMAJORCLSS=ths.ASSETCODE and ths.ASSETCODE='1' " & _
"GROUP BY ths.ACCOUNTID, ioa.ASSETMAJORCLSS, ioa.MINALLOC, ioa.MAXALLOC " & _
"HAVING sum(val(ths.INSTCONCENTRATION))>=ioa.minalloc and sum(val(ths.INSTCONCENTRATION))<=ioa.maxalloc "), dbFailOnError

mytextbox.value = db.execute "Select count(qryvar.ACCOUNTID) from qryvar"

donisanp

ASKER

Used an alternate method which incorporated the querydef function based on your suggestion. Thanks for the help!

rockiroads

Well why not save this as a query as suggested before then use dcount

mytextbox.value = DCOUNT("fieldname","tableORqueryname")

eg

mytextbox.value = DCOUNT("ACCOUNTID","qryvar")

rockiroads

If u got columns with null values in accountid, then use "*" instead of "accountid"

donisanp

ASKER

You've read my mind. I assigned the query to a string, then executed it as a querydef:

DIM selAcct as string
DIM qryAcct as QueryDef

selAcct = "SELECT DISTINCT ths.ACCOUNTID, ioa.ASSETMAJORCLSS, ioa.MINALLOC, ioa.MAXALLOC, sum(val(ths.INSTCONCENTRATION)) " & _
"FROM & tablename & AS ths, tblIOAllocation AS ioa " & _
"WHERE ioa.INVSOBJECTIVE=ths.INVSOBJECTIVECD and ioa.ASSETMAJORCLSS=ths.ASSETCODE and ths.ASSETCODE='1' " & _
"GROUP BY ths.ACCOUNTID, ioa.ASSETMAJORCLSS, ioa.MINALLOC, ioa.MAXALLOC " & _
"HAVING sum(val(ths.INSTCONCENTRATION))>=ioa.minalloc and sum(val(ths.INSTCONCENTRATION))<=ioa.maxalloc "

Set qryAcct = ("qryAccounts", selAcct)

mytextbox.value = DCOUNT("*","qryAcCounts")

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

'Fred' ? Why not Freddie ?

mx

donisanp

ASKER

Fred definitely sounds more professional...

rockiroads

mx, different ways to spelly freddy so leads to confusion :)

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

There is only one way to spell Freddie ... as in King ...

:-)

mx