How can I use a SQL formatted query in VBA Code? 1

[nonturbo]

Hey guys,
I created a query with the Query builder and tested it. It works fine. Basically, I want the query to count the number of Active accounts at any given time. It should just return a number, ie 154. I want to be able to use the result, as in our example 154 in VBA code, but I'm not sure how to go about doing this.

Here's the working Query:

SELECT Count(Partners.[Account Nbr]) AS Total
FROM Partners
WHERE (((Partners.[Status ID])=1));

And I need set MyPeriod![Number of Partners] = to the value of the query. Got it? Please help! Thanks,

NT

 

[Junior1544]

Sorry, i only know how to use dao, not ado... so this is in dao format...

and i'm not possative this will work, it's just a guess...

--James



dim rst as dao.recordset

set rst = currentdb.openrecordset("SELECT Count(Partners.[Account Nbr]) AS Total FROM Partners WHERE (((Partners.[Status ID])=1));&quot

MyPeriod![Number of Partners] = rst!count

rst.close
set rst = nothing

junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[jebry]

Hi!

What you can do depends on how you intend to get the information into MyPeriod![Number of Partners]. If you have a standard data entry form for MyPeriod (which I am assuming is a table) then in the form open event procedure you would use the following:

Me!YourTextBox = DLookUp("Total", "YourQuery&quot

That said, I wouldn't recommend storing this information since it is dynamic and can be displayed anytime in forms or reports using the DLookUp shown above. Just set the control source of the text box to =DLookUp("Total", "YourQuery&quot and the correct value will be displayed.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[gol4]

Not that I'm a fan of aggregate functions but here is another option

MyPeriod![Number of Partners] = dCount("[Account Nbr]","Partners","[Status ID]=1&quot