Sum of Values between Dates

[Tieval]

Hi,

I am reviewing some terrible old database methods and was wondering the best way to achieve a text box containing the sum of values for items between two dates. At present it is done using a sub-form based on a query of a query.

At first a query collects the values between dates:

SELECT tblDespatch.Invoiced, tblOrders.Value
FROM tblOrders INNER JOIN tblDespatch ON tblOrders.ID = tblDespatch.JobDespatchID
WHERE (((tblDespatch.Invoiced) Between (DateAdd("m",-3,Date())) And Date()));

and then a secondary query generates the sum of the values:

SELECT Sum(qrySWBI03.Value) AS SumOfValue
FROM qrySWBI03;

This actually works perfectly but needs two queries and a sub-form for each bit of information and there twelve of them (quotes, orders and invoices for last 3, 6, 9 and 12 months).

 

[plog]

the best way to achieve a text box containing the sum of values for items between two dates

For that I would recommend basing the control source of your text box on a DSUM (http://www.techonthenet.com/access/functions/domain/dsum.php) that does the calculation for you.

Also, your query doesn't require a form or a subform--it's a query just based on tblOrders and tblDespatch. So, I don't know how using a subform for the totals figures into this.

Lastly, your SQL could be combined. There's no need for a sub query--just do your summing on the Value field in the first query you posted. Very lastly, 'Value' is a poor choice for a field name--its a reserved word (https://support.microsoft.com/en-us/kb/286335) and using it as a name makes coding and querying a little more difficult. I suggest you rename that field by prefixing what the value represents to the name (OrderValue, ProductValue, etc.).

 

[Tieval]

Many thanks, I have now got the DSum working quite well but am struggling with the between dates bit.

I have yet to rid the Value name and am not too worried about the cross table bit (left out for now) but can you advise on the following bit of failed code for a text box, it works with fixed dates but I am trying to achieve the last three months.

=DSum("Value","tblOrders","[Date] between (DateAdd("m",-3,Date())) And (Date())")

 

[plog]

In your query you have the date field in tblOrders as 'Invoiced', in your DSum you call it [Date]. Which is it?

I avoid 'Between' and just use math symbols so my criteria would be this:

"[Invoiced]>=#" & DateAdd("m", -3, Date()) & "# AND [Invoiced]<=#" & Date() & "#"

 

[Tieval]

Works a treat, many thanks for this, suspect the database size may reduce a lot!