Use SQL results

[NISMOJim]

Hello,


I'm trying to figure out code to run a query to return the sum of a field between a date range, and am having little luck with it. Here's what I have so far;

strSQLLastTotal = "Select Quantity AS SumOfQuantity FROM Transactions WHERE Date between " & LastDateStart & " And " & LastDateEnd
DoCmd.RunSQL strSQLLastTotal

The code further up in the module calculates the LastDateStart & LastDateEnd, and in looking in the immediate window, the dates come out perfect. Not sure if the SQL syntax is correct, but I started it out as a simple query before copying the SQL to the VBA module, and it did return a single number.

I need to use this resulting number further down in the code. How can I get some kind of variable to store the resulting number of the SQL statement?

Hope that made sense, and thanks for any help

[JoeM]

One way would be to create the query code (like you have), assign it to a query (using "CurrentDB.QueryDefs(myQueryName).SQL=strSQLLastTotal"), and use the DLOOKUP function to look up the value and store it in a variable.

Note, I am not sure if you are trying to Sum the Quantity field, but if so, you would need to use the SUM function in your statement, i.e.

Select Sum(Quantity) AS SumOfQuantity...

You may also need to put qualifiers around your dates in your criteria.

[NISMOJim]

Thanks for the reply. Let me start with the smaller stuff first...

I am trying to sum the Quantity field, so I applied the change you suggested. I guess changing a query to SQL view doesn't translate exactly to VBA when you copy/paste.
Also, I'll check on the date qualifiers (I think you mean the #'s) when I get the first part going. So far, they seem to work ok, but that may change in the SQL statement.
You lost me on the first part of your reply. This is all being done in VBA, so there is no actual query as I understand it, that you can look at like a table or form. Let me see if I follow you...

Leave my VBA part as is...

strSQLLastTotal = "Select Sum(Quantity) AS SumOfQuantity FROM Transactions WHERE Date between " & LastDateStart & " And " & LastDateEnd
DoCmd.RunSQL strSQLLastTotal

Add then the part you put in parentheses would go on the next line...

CurrentDB.QueryDefs(myQueryName).SQL=strSQLLastTotal

where (MyQueryName) will be the name of a physical query that this code creates? Next, I'm not sure how to do a DLookUp in VBA. Once I get this SumofQuantity value, I need to use it in several more calculations in the continuing code, so I assume the DLookUp will bring the value back into the VBA code as a variable to use later?
Lastly, I think I would like to see this value in the Immediate window as I'm working on it to make sure I'm on the right track as things progress. Once the value is assigned to a variable, can I just add a debug.print VariableName line afterwards to see the results?

Thanks again. When I start these things I usually get in way over my head, so please be patient with me. I'll be back with more questions on how to work more of the code, but one step at a time.

[NISMOJim]

I'm not sure how it all works, but isn't there a way to run the SQL then save it as a recordset? Since the query will only return a single value, it seems that there should be a way to be able to save it to work with later. I'll need to do this a few times in the module with different dates & different records, then do some calculating later on using these numbers, along with others that will be way more complicated to pull out of the table, but I'll get to that part later.

[ssanfu]

Hello,
I'm trying to figure out code to run a query to return the sum of a field between a date range, and am having little luck with it. Here's what I have so far;

strSQLLastTotal = "Select Quantity AS SumOfQuantity FROM Transactions WHERE Date between " & LastDateStart & " And " & LastDateEnd
DoCmd.RunSQL strSQLLastTotal

I'm not sure how it all works, but isn't there a way to run the SQL then save it as a recordset?

Nope....

From Help:

You can use the RunSQL action to run a Microsoft Access action query by using the corresponding SQL statement.

So an action query is an Append, Delete, Make-table or an Update query. A "SELECT" query cannot be executed using "DoCmd.RunSQL".

One way would be to create the query code (like you have), assign it to a query (using "CurrentDB.QueryDefs(myQueryName).SQL=strSQLLastTotal")

What JoeM is telling you:
Create a query
In the grid, add the table "Transactions"
Add the field "Quantity".
Save the query - lets call it "CalcQuanity".

This is now a saved query.
In your code, (BTW - dates need delimiters I added them)

strSQLLastTotal = "Select Sum(Quantity) AS  SumOfQuantity FROM Transactions WHERE Date between #" & LastDateStart  & "# And #" & LastDateEnd & "#"

CurrentDB.QueryDefs("CalcQuanity").SQL=strSQLLastTotal

The SQL of the saved query "CalcQuanity" has been changed from the simple select string to the totals query SQL.
Now , using DLOOKUP(), you can get the value from the saved query "CalcQuanity".

In the code, you would have a line something like:

SomeVariable = DLOOKUP("SumOfQuantity", "CalcQuanity")

-------------------------------------

Another way would be to open a recordset in code (VBA).
(Warning - air code)

Dim r as DAO.Recordset
Dim strSQLLastTotal as String
.
.
.
.
.
strSQLLastTotal = "Select Sum(Quantity) AS  SumOfQuantity FROM  Transactions WHERE Date between #" & LastDateStart  & "# And #"  & LastDateEnd & "#;"
Set r = Currentdb.OpenRecordset(ssql)

'check for records
If Not (r.BOF and r.EOF) Then
   LastTotal = r("SumOfQuantity")
Else
   LastTotal = 0
End If
.
.
.
.
r.close 
Set r = nothing

[Dunro]

Using a recordset is likely the easiest approach. ssanfu's code looks good.

I'd be careful with including dates in SQL statements unless they will always be in mm/dd/yyyy format. (In Canada, for some crazy reason, the regional default is dd/mm/yyyy - so I have to make sure the dates get changed back to American format before being appended to the SQL statement otherwise Access gets confused).

I also would not name the field Date if you can help it. This is a reserved word and in some cases Access doesn't like that.

[NISMOJim]

I went with the recordset method and added times in my dates variables to include the whole last day. This is working perfectly!! The first part of my monthly report writes itself. Now on to more coding. I will have more questions to complete this code, so I don't know if i should continue here, or start a new thread. Either way, thank you so much. Because of you guys, everyone at work thinks I know what I'm doing!

[ssanfu]

Because of you guys, everyone at work thinks I know what I'm doing!

Been there, done that, got the T-shirt........