run sum in the form

[Irene]

i have a form which is have field called part code, autonumber, del date,
order qty and so on. now i would like to calcualte the run sum in the form.
eg,

Autonumber part code del date order qty
sum
1 a04-p01 1/5/08
1000 1000
2 a04-p01 2/5/08
1000 2000
3 a04-p01 3/5/08
2000 4000

can make it? i want calculate as above. i want it calculate as the sum
field. hope somebody can help me. Thanks!

 

[Allen Browne]

You could try a text box with something like this in the Control Source:

=DSum("Qty", "Table1", "([part code] = """ & [part code] &
""") AND ([del date] < " &
Format(Nz([del date]+1, #1/1/9999#), "\#mm\/dd\/yyyy\#") & ")")

That's all one line. Replace Table1 with your table name.

This won't work properly if:
a) There are multiple entries for a [part code] on the same [del date].

b) The form is not sorted by [part code] and [del date].

c) The form is filtered.

If this is for display purposes only (i.e. you don't need to edit the
values), it would be quicker to use a subquery rather than the DSum()
expression. Details:
http://allenbrowne.com/subquery-01.html#Aggregation

 

[Irene]

thanks for the reply. my form actually is a sub form. it is link with the
part code with the main form. this subform is based on a query. so how i can
do the runsum on the sub form? i want sort by del date and autonumber, can?

thanks!

You could try a text box with something like this in the Control Source:

=DSum("Qty", "Table1", "([part code] = """ & [part code] &
""") AND ([del date] < " &
Format(Nz([del date]+1, #1/1/9999#), "\#mm\/dd\/yyyy\#") & ")")

That's all one line. Replace Table1 with your table name.

This won't work properly if:
a) There are multiple entries for a [part code] on the same [del date].

b) The form is not sorted by [part code] and [del date].

c) The form is filtered.

If this is for display purposes only (i.e. you don't need to edit the
values), it would be quicker to use a subquery rather than the DSum()
expression. Details:
http://allenbrowne.com/subquery-01.html#Aggregation

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

i have a form which is have field called part code, autonumber, del date,
order qty and so on. now i would like to calcualte the run sum in the
form.
eg,

Autonumber part code del date order qty
sum
1 a04-p01 1/5/08
1000 1000
2 a04-p01 2/5/08
1000 2000
3 a04-p01 3/5/08
2000 4000

can make it? i want calculate as above. i want it calculate as the sum
field. hope somebody can help me. Thanks!

 

[Irene]

thanks for the reply. my form actually is a sub form. it is link with the
part code with the main form. this subform is based on a query. so how i can
do the runsum on the sub form? i want sort by del date and autonumber, can?

thanks!

You could try a text box with something like this in the Control Source:

=DSum("Qty", "Table1", "([part code] = """ & [part code] &
""") AND ([del date] < " &
Format(Nz([del date]+1, #1/1/9999#), "\#mm\/dd\/yyyy\#") & ")")

That's all one line. Replace Table1 with your table name.

This won't work properly if:
a) There are multiple entries for a [part code] on the same [del date].

b) The form is not sorted by [part code] and [del date].

c) The form is filtered.

If this is for display purposes only (i.e. you don't need to edit the
values), it would be quicker to use a subquery rather than the DSum()
expression. Details:
http://allenbrowne.com/subquery-01.html#Aggregation

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

i have a form which is have field called part code, autonumber, del date,
order qty and so on. now i would like to calcualte the run sum in the
form.
eg,

Autonumber part code del date order qty
sum
1 a04-p01 1/5/08
1000 1000
2 a04-p01 2/5/08
1000 2000
3 a04-p01 3/5/08
2000 4000

can make it? i want calculate as above. i want it calculate as the sum
field. hope somebody can help me. Thanks!

 

[Allen Browne]

As suggested, the DSum() expression calculates the quantity only for the the
[part code] in your form, so that part should be okay.

If your subform is based on a query that has other criteria/joins that limit
it further, you will need to modify the the 3rd argument to include those
criteria as well.

Presumably the query for your subform sorts first by [del date] and then the
autonumber. Unfortunately, DSum() is not really powerful enough for that.
You could do that in a subquery, but it would give read-only results.
Alternatively, you could write your own function that performs the sum, and
sorts correctly.

I don't have such an extented DSum() for you, but it would be a matter of
passing an extra argument like this extended DLookup() does:
http://allenbrowne.com/ser-42.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

thanks for the reply. my form actually is a sub form. it is link with the
part code with the main form. this subform is based on a query. so how i
can
do the runsum on the sub form? i want sort by del date and autonumber,
can?

thanks!

You could try a text box with something like this in the Control Source:

=DSum("Qty", "Table1", "([part code] = """ & [part code] &
""") AND ([del date] < " &
Format(Nz([del date]+1, #1/1/9999#), "\#mm\/dd\/yyyy\#") & ")")

That's all one line. Replace Table1 with your table name.

This won't work properly if:
a) There are multiple entries for a [part code] on the same [del date].

b) The form is not sorted by [part code] and [del date].

c) The form is filtered.

If this is for display purposes only (i.e. you don't need to edit the
values), it would be quicker to use a subquery rather than the DSum()
expression. Details:
http://allenbrowne.com/subquery-01.html#Aggregation

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

i have a form which is have field called part code, autonumber, del
date,
order qty and so on. now i would like to calcualte the run sum in the
form.
eg,

Autonumber part code del date order
qty
sum
1 a04-p01 1/5/08
1000 1000
2 a04-p01 2/5/08
1000 2000
3 a04-p01 3/5/08
2000 4000

can make it? i want calculate as above. i want it calculate as the sum
field. hope somebody can help me. Thanks!