Query result into a variable

[yippie_ky_yay]

Hello,

I'm trying to use a query result in my code and am obviously a little unclear on something.

What I have is:

Dim MyResult
MyResult = DoCmd.RunSQL("...sql statement that I know works ...")

The SQL statement is a sum - so it only returns 1 result which is why I thought the compiler would be ok with it.

Is there something I'm missing? The error message I get is "Expected Function or Variable" - and the ".RunSQL" is what's highlighted.

Any help would be greatly appreciated!

-Sean

 

[The_Doc_Man]

You cannot do it this way. RunSQL isn't a function that returns a value like you think it does. It might return a status code, but that's about it.

The ONLY time you want to run an SQL query is if it is NOT a SELECT query (with or without aggregate functions). You run SQL on action queries. I.e. you only run SQL to MakeTable, Update, Delete, or Append. A SELECT query can be run but does nothing. (As you have discovered.)

You need to look up the DSum function, I think.

 

[yippie_ky_yay]

Thank you Doc_Man - DSum was what I needed (and it was a lot easier than my query). Thanks for the extra info too!

-Sean