Calculate a Total

[sgfromny]

Someone please help this Newbie!, I have a table with project numbers, dates and costs. I want to be able display the total cost of all projects between date "a" and "b"..

Ive set up a query to do this, in the cost field Ive set "sum" in the total field and have put in the date parameters in the date areas. The query returns all the projects in the date range but does not total all the costs.

I want the total of these projects to appear in a test box on a form.

Any help is appreciated.

Thanks
Steve

 

[OpalBiz]

Steve

There is probably a better way, but I've encountered the same problem and elected to use a list box rather than a text box to display summary totals. A list box has a rowsource property to perform queries unlike a text box. I haven't tried the following code yet but it should be something similar:

**************Code Starts Here*******************
With Me.lstSumTotal
.Rowsource = _
"SELECT SUM([FieldName]) FROM TableName & _
"WHERE Date1 >= " & Chr(34)& Me.txtDate1 & Chr(34) & _
&quot;AND Date2 <= &quot; & Chr(34) & Me.txtDate2 & Chr(34)
End With
***************Code Ends Here********************

See if that works,
John

 

[sgfromny]

Is it safe to assume that in my query stated in my previous post, the results Im getting are normal, by normal I mean: when I select the totals button and select sum, i DONT get a grand total on all the records. I get a list of records that meet the criteria without a bottom line total cost.

This being said, Looking at your code example, I should have been more clear to mention that the query I have created actually gets its info from multiple tables. The dates come from one table the costs and project info come from another, along with I have a criteria on another field.

The Goal is the Sum of all costs that meet the query's criteria.

Thanks
Steve

 

[OpalBiz]

Sorry Steve, I'm a little slow. I'll try to help. What does your existing code look like that queries the list of records that fall within the date parameters? Are you looking for one summary total of the detail queried in the existing list?

 

[sgfromny]

Thanks OpalBiz, I got it to work, not sure how.