calculationg the Sum of only selected dates

  • Thread starter Thread starter mark
  • Start date Start date
M

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
 
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
 
mark said:
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)
Click to expand...

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))
 
Back
Top