How to use query results in variables

[Stuartp]

How can I run a query and store the result of it in a variable, e.g. if my query returns the result 4 then i want to store it in a "days" variable, so I can say things like if days=4 then.... and so on.

Is VBA code the right way to do this, and if so how?

Many thanks

Stuart

 

[Craig0201]

Stuart,

The query will not return a variable. It will return a recordset. In order to get at the data, you will need to use either ADO or DAO. You can then use this to get your data into a variable or array.

Give me a shout if you need any more help.

Craig

 

[Stuartp]

thanks for your reply. yes i think i will need more help!! how would i use ado or dao in my access application to achieve this?

Thanks

Stuart

 

[pcdaveh]

Two days ago I had the same question and this is how I handled it.

First let me set up my scenario so you can modify it to your situation. I have a Form that lists transactions for a customer. Each customer of course has a total. To get the total I had to create a sum query of the customers records. The result of this query is one value per customer for any given date range. I copied that SQL statement and put it in my vb code. I created a listbox on my form and in code referenced the listbox. The listbox is very small and will only ever have one value in it. It is used instead of the text box because it has a rowsource that can be set to the sql statement. example not taking into account word wrap on this screen.

lstAcctBalance.RowSource = "SELECT Format(Sum([AccountBalance]),'$#,###.##') AS AcctBal FROM q_TransactionsList;"



'I created a module outside of the Form with a Public
'variable named TransActionTotalFormLoad . The statement
'below reads the value of the listbox into the variable.

TransActionTotalFormLoad = lstAcctBalance.ItemData(0)


'Hope this helps. David Harris pcdaveh

 

[jebry]

Hi!

There are other ways to get the information. Look up the DSum, DCount and DLookup functions just to name a few. Altogether they are called Domain Aggregate Functions and have the general format DSum(Field, Table or Query, Criteria).

hth
Jeff Bridgham