?
Solved

ACCESS VBA: Count Query of a SQL String

Posted on 2009-04-21
10
Medium Priority
?
1,056 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:donisanp
  • 4
  • 4
  • 2
10 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 24197721
When u mean assign a query name, do you mean use a query name in the execute command? if so then yes it can be done

saved query called qryFred

you can do

db.Execute "qryFred",dbFailOnError
0
 

Author Comment

by:donisanp
ID: 24198044
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"
0
 

Author Closing Comment

by:donisanp
ID: 31572917
Used an alternate method which incorporated the querydef function based on your suggestion.  Thanks for the help!
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Expert Comment

by:rockiroads
ID: 24199491
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")


0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24199501
If u got columns with null values in accountid, then use "*" instead of "accountid"
0
 

Author Comment

by:donisanp
ID: 24199700
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")
0
 
LVL 75
ID: 24199722
'Fred' ?  Why not Freddie ?

mx
0
 

Author Comment

by:donisanp
ID: 24199729
Fred definitely sounds more professional...
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24201750
mx, different ways to spelly freddy so leads to confusion :)
0
 
LVL 75
ID: 24201941
There is only one way to spell Freddie ... as in King ...

:-)

mx
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

578 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