calculationg the Sum of only selected dates

[mark]

Hi

Any help welcome

I have a form, generated from a query which displays all
the CURRENT 'jobs' that are in the factory production
system. Presently, I have a txtbox in the footer of the
form which calculates the total of one particular
field/column. (this column being 'No of Panels'). This
would instantly tell us the total 'panel' load for the
shop floor at the present time.
There is also a 'delivery date' field/column displayed on
the form, relating to each production job.
There are obviously a number of other fields/columns in
the form, but not relevant to my question.
What I want to do, is to have a similar sum txt box that,
use the 'Delivery date' values, but ONLY calculates the
total 'No of Panels' for the present month. Then i can
have another txtbox/or similar that can perhaps display
the 'overall' shop floor panel load.(regardless of date)

Any help would be appreciated

Mark

 

[Ayelet]

Hi
Try using something like this in the new txtbox's Control
Source:

DSUM("[NoOfPanels]", "MyQueryName", "[DeliveryDate] >=
(Date() - 30)")

(That's DSUM(<name of field>, <name of table>, <where
condition>))
I'm not completely sure about the details (you might want
to use Month() function for the date comparison, for
example), but this is the general idia.
HTH,
Ayelet

 

[Marshall Barton]

I have a form, generated from a query which displays all
the CURRENT 'jobs' that are in the factory production
system. Presently, I have a txtbox in the footer of the
form which calculates the total of one particular
field/column. (this column being 'No of Panels'). This
would instantly tell us the total 'panel' load for the
shop floor at the present time.
There is also a 'delivery date' field/column displayed on
the form, relating to each production job.
There are obviously a number of other fields/columns in
the form, but not relevant to my question.
What I want to do, is to have a similar sum txt box that,
use the 'Delivery date' values, but ONLY calculates the
total 'No of Panels' for the present month. Then i can
have another txtbox/or similar that can perhaps display
the 'overall' shop floor panel load.(regardless of date)

You can use an expression inside the Sum function. Try
something like:

=Sum(IIf(Format([Delivery Date], "yyyy mm") = Format(Date(),
"yyyy mm"), [No of Panels], 0))