Form Footer Sum

[mreference]

I'm sure this is easy, but I cannot find the solution anywhere.

I have a subform that displays all orders placed based on an item code.

Ord_Date = Date order was placed
Qty = items ordered

I want to display a total in the form footer that tells me how many items were ordered in the last 7 days, 30 days and 90 days

For example, the query will filter on one particular product and list in a continuous subform all the orders ever made on that product. In the footer I am looking to display the total number bought over 7, 30 and 90 days.

Please can somebody help me.

 

[boblarson]

You can use DSum for each of the text boxes. For example, the one for 7 days:

=DSum("Qty", "YourTableNameHere", "[ItemCode]=" & [ItemCodeField] & " And [Ord_Date] Between #" & DateAdd("d", -7, Date()) & "# And #" & Date() & "#")

Where ItemCode is the actual name of the product code field in the table and [ItemCodeField] is the name of the control bound to the field in this form. And YourTableNameHere needs to be replaced with the actual name of your table.

And if the ItemCodeID is a text value then you need quotes too.

=DSum("Qty", "YourTableNameHere", "[ItemCode]=" & Chr(34) & [ItemCodeField] & Chr(34) & " And [Ord_Date] Between #" & DateAdd("d", -7, Date()) & "# And #" & Date() & "#")