Storing the query result using VBA

  • Thread starter Thread starter Transliteration
  • Start date Start date
T

Transliteration

Dear Newsgroup users.


I am trying to write VBA code within a Access which will store the result of
a query.

Say that I want to extract the maximum date stored in a table. The following
works but displays the result on the screen:


DoCmd.OpenQuery QueryName:="qryTestMaxDate"


I want to be able to store the result in a variable, for example


dtMax = DoCmd.OpenQuery(QueryName:="qryTestMaxDate")

But the compiler does not like that because Do.CmdOpenQuery is not a
function.

I tried using ADO but then I got an error saying that the database I am
trying to access is being used, which is true. I am using it to write and
test my code


What is the simplest way to store query results into a variable?

Many thanks


Ilan
 
Transliteration said:
Dear Newsgroup users.


I am trying to write VBA code within a Access which will store the result of
a query.

Say that I want to extract the maximum date stored in a table. The following
works but displays the result on the screen:


DoCmd.OpenQuery QueryName:="qryTestMaxDate"


I want to be able to store the result in a variable, for example


dtMax = DoCmd.OpenQuery(QueryName:="qryTestMaxDate")

But the compiler does not like that because Do.CmdOpenQuery is not a
function.

I tried using ADO but then I got an error saying that the database I am
trying to access is being used, which is true. I am using it to write and
test my code


What is the simplest way to store query results into a variable?
Click to expand...


dtMax = DMax("DateFieldName", "qryTestMaxDate")
 
Thank you for your reply.

You have answered the specific question I posed. Could you give me a more
general reply? Supposing I have a query which returns a scalar. How can I
store it into a variable?

For example, say I have a query called qryWhichReturnsAScalar. How do I go
about assigning it to a variable, as follows.

myValue = DoSomethingTo qryWhichReturnsAScalar

Thanks once again.



Ilan
 
Transliteration said:
Thank you for your reply.

You have answered the specific question I posed. Could you give me a more
general reply? Supposing I have a query which returns a scalar. How can I
store it into a variable?

For example, say I have a query called qryWhichReturnsAScalar. How do I go
about assigning it to a variable, as follows.

myValue = DoSomethingTo qryWhichReturnsAScalar

Thanks once again.



Ilan
Click to expand...

Check help file for DLookup()
 
Back
Top