Need Assistance Please -- Count/Sum Function Queries

Cly

New member
Local time
Today, 14:59
Joined
Mar 14, 2005
Messages
5
I am new to Access VBA. I am having a problem passing values from an aggregate function query, ( Select Count([ID]) from tblWhatever) ) and I can't seem to figure out how to pipe the data to an array.

I.E.

dim sql as string
dim rst as adodb.recordset
dim cnn as adodb.connection
dim varsqlresults as variant
set cnn = currentproject.connection
set rst = new adodb.recordset

sql = "Select count([Id]) from tblMaster"
rst.open " " & sql & " ",cnn, adopendyanmic,adlockreadonly
varsqlresults = rst.getrows

msgbox (varsqlresults)

I recieve a type mismatch. (Understandable.) What I am trying to do is pull the results from the aggregate function query to a single dimension array and populate a text box with the value of that variable. Any Help would be GREATLY appreciated.
 
your almost there, except you need to define which element within the array you wish to display.


ie , to display the count use the first element in the array (0,0)


Code:
dim sql as string
dim rst as adodb.recordset
dim cnn as adodb.connection
dim varsqlresults as variant
set cnn = currentproject.connection
set rst = new adodb.recordset

sql = "Select count([Id]) from tblMaster"
rst.open " " & sql & " ",cnn, adopendyanmic,adlockreadonly
varsqlresults = rst.getrows

msgbox (varsqlresults(0,0))


Note the array created by getrows is always multidimenional.
 
Thank you.

Thank you tremendously. This is a HUGE time saver.

--Cly
 

Users who are viewing this thread

Back
Top Bottom