Dsum or other method to calculate running total spend.


G

Guest

I have a form and subform for a student course booking system and want to
display the total spent for the student throught the year in the main part of
the form.

In the sub form I enter the subsistance paid, the travelling and course fee.
I total these in a calculated control to show the total cost for the student
in that transaction.

How can I maintain a running total of total cost for each student, ie
display the total costs (spend to date) for each student over a period in the
main form? I have tried using dsum to sum the individual values in the
course table but am having a problem witht the syntax. Please advise. Thanks
in advance.
 
Ad

Advertisements

M

Michel Walsh

Hi,




DSum("expression", "tableName", "dateStamp<=" & Format( Me.DateStamp ,
"\#mm-dd-yyyy\#") )


where Me.DateStamp refers to the control of the form, and dateStamp refers
to the field in the table.. Expression can be a single field name, or many
fields and constants implied in an arithmetic expression, but the argument
is supplied as a string..



Hoping it may help,
Vanderghast, Access MVP
 
G

Guest

Hi Michel

thanks for this. I'm still not clear on how to get the totals for the
record currently displayed. I have tried the syntax

=DSum("[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]","study_leave_recs")

and this works o.k. I then tried using it with the select criteria as
follow and it fails.

=DSum("[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]","study_leave_recs","surname=" & me.[surname])

where name is the surname field on the main form for which I want the
totals for. What am I doing wrong?!?!
Thanks again. Peter
 
T

Tom Ross

surname is a text conrol that needs quotation marks

I like to use single quotes within the double quotes to mark text. You have
to add a bit a the end to hold the enclosing single quotes


=DSum("[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]","study_leave_recs","surname
='" & me.[surname]) & "'"

(spread out for emphasis "surname = ' " & me.[surname] & " ' ")




PETER said:
Hi Michel

thanks for this. I'm still not clear on how to get the totals for the
record currently displayed. I have tried the syntax

=DSum("[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]","study_leave_recs")

and this works o.k. I then tried using it with the select criteria as
follow and it fails.
=DSum("[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]","study_leave_recs","surname
=" & me.[surname])
 
N

Neil

Peter,

When searching for text like this, you need to enclose the search criteria
with '. For e.g.

=DSum("[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]","study_leave_recs","surname='"
& me.[surname]) & "'"

Watch when searching for dates this way too. They need to be enclosed with
#.

=DSum("[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]","study_leave_recs","SomeDate>#"
& me.[SomeDate]) & "#"

HTH,

Neil.

PETER said:
Hi Michel

thanks for this. I'm still not clear on how to get the totals for the
record currently displayed. I have tried the syntax

=DSum("[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]","study_leave_recs")

and this works o.k. I then tried using it with the select criteria as
follow and it fails.

=DSum("[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]","study_leave_recs","surname="
& me.[surname])

where name is the surname field on the main form for which I want the
totals for. What am I doing wrong?!?!
Thanks again. Peter

Michel Walsh said:
Hi,




DSum("expression", "tableName", "dateStamp<=" & Format( Me.DateStamp
,
"\#mm-dd-yyyy\#") )


where Me.DateStamp refers to the control of the form, and dateStamp
refers
to the field in the table.. Expression can be a single field name, or
many
fields and constants implied in an arithmetic expression, but the
argument
is supplied as a string..



Hoping it may help,
Vanderghast, Access MVP
 
G

Guest

Thanks for this. I am still having problems though. When I try to look at
the form I get #Name? in the control. I notice that the

"surname='" & me.[surname]) & "'" part gets changed to

"surname ='" & [me].[surname]) & "'"

Also I'm not clear as to why you use the & "'" at the end.

Sorry to be a pain. Hope you can advise. Thanks again.

Tom Ross said:
surname is a text conrol that needs quotation marks

I like to use single quotes within the double quotes to mark text. You have
to add a bit a the end to hold the enclosing single quotes


=DSum("[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]","study_leave_recs","surname
='" & me.[surname]) & "'"

(spread out for emphasis "surname = ' " & me.[surname] & " ' ")




PETER said:
Hi Michel

thanks for this. I'm still not clear on how to get the totals for the
record currently displayed. I have tried the syntax

=DSum("[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]","study_leave_recs")

and this works o.k. I then tried using it with the select criteria as
follow and it fails.
=DSum("[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]","study_leave_recs","surname
=" & me.[surname])
where name is the surname field on the main form for which I want the
totals for. What am I doing wrong?!?!
Thanks again. Peter
 
Ad

Advertisements

T

Tom Ross

Do you need to have the running sum in your report, or can it be a report
generated from the form.
The report wizard has a running sum option as I remember. Maybe a simplier
solution than tryint to make
the form do it.
Tom
 
A

Andrea Jones

In design mode click the top left corner of the form so there is a black
square in it and click the properties icon. Under the Event tab enter the
following code for the OnCurrent event:

Private Sub Form_Current()
Dim a As Double
a = Nz(DSum("[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]", "study_leave_recs",
"[Surname]= ' " & Me![Surname] & " ' "), 0)
Me![TotalCost] = a
End Sub

The TotalCost field is a field that you create in the table which your
parent fields are taken from. When you add this field to your parent form
you should see the total appear. I've added the Nz function to the DSum to
preventing you getting 'Invalid use of null' messages when you don't have
any sub-records for a particular person. I think you were having problems
becuase you had [Me]. instead of Me! in your expression, the & "'" converts
the Me!Surname into a string with single quotes around it e.g. 'Jones'
inside a pair of double quotes so Access sees "'Jones'" as the string to
match.

Andrea Jones
http://www.allaboutoffice.co.uk

PETER said:
Thanks for this. I am still having problems though. When I try to look at
the form I get #Name? in the control. I notice that the

"surname='" & me.[surname]) & "'" part gets changed to

"surname ='" & [me].[surname]) & "'"

Also I'm not clear as to why you use the & "'" at the end.

Sorry to be a pain. Hope you can advise. Thanks again.

Tom Ross said:
surname is a text conrol that needs quotation marks

I like to use single quotes within the double quotes to mark text. You have
to add a bit a the end to hold the enclosing single quotes


=DSum("[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]","study_leave_recs","surname
='" & me.[surname]) & "'"

(spread out for emphasis "surname = ' " & me.[surname] & " ' ")




PETER said:
Hi Michel

thanks for this. I'm still not clear on how to get the totals for the
record currently displayed. I have tried the syntax

=DSum("[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]","study_leave_recs")

and this works o.k. I then tried using it with the select criteria as
follow and it fails.
=DSum("[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]","study_leave_recs","surname
=" & me.[surname])
where name is the surname field on the main form for which I want the
totals for. What am I doing wrong?!?!
Thanks again. Peter

:

Hi,




DSum("expression", "tableName", "dateStamp<=" & Format( Me.DateStamp ,
"\#mm-dd-yyyy\#") )


where Me.DateStamp refers to the control of the form, and dateStamp refers
to the field in the table.. Expression can be a single field name,
or
many
fields and constants implied in an arithmetic expression, but the argument
is supplied as a string..



Hoping it may help,
Vanderghast, Access MVP

I have a form and subform for a student course booking system and
want
to
display the total spent for the student throught the year in the
main
part
of
the form.

In the sub form I enter the subsistance paid, the travelling and course
fee.
I total these in a calculated control to show the total cost for the
student
in that transaction.

How can I maintain a running total of total cost for each student, ie
display the total costs (spend to date) for each student over a
period
in
the
main form? I have tried using dsum to sum the individual values
in
the
course table but am having a problem witht the syntax. Please advise.
Thanks
in advance.
 
Ad

Advertisements

T

Tom Ross

Peter
Also I'm not clear as to why you use the & "'" at the end

This is so the access interpreter will see something like
surname='anderson' instead of surname='anderson which is an error for
unbalanced quotes.

Tom



PETER said:
Thanks for this. I am still having problems though. When I try to look at
the form I get #Name? in the control. I notice that the

"surname='" & me.[surname]) & "'" part gets changed to

"surname ='" & [me].[surname]) & "'"

Also I'm not clear as to why you use the & "'" at the end.

Sorry to be a pain. Hope you can advise. Thanks again.

Tom Ross said:
surname is a text conrol that needs quotation marks

I like to use single quotes within the double quotes to mark text. You have
to add a bit a the end to hold the enclosing single quotes


=DSum("[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]","study_leave_recs","surname
='" & me.[surname]) & "'"

(spread out for emphasis "surname = ' " & me.[surname] & " ' ")




PETER said:
Hi Michel

thanks for this. I'm still not clear on how to get the totals for the
record currently displayed. I have tried the syntax

=DSum("[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]","study_leave_recs")

and this works o.k. I then tried using it with the select criteria as
follow and it fails.
=DSum("[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]","study_leave_recs","surname
=" & me.[surname])
where name is the surname field on the main form for which I want the
totals for. What am I doing wrong?!?!
Thanks again. Peter

:

Hi,




DSum("expression", "tableName", "dateStamp<=" & Format( Me.DateStamp ,
"\#mm-dd-yyyy\#") )


where Me.DateStamp refers to the control of the form, and dateStamp refers
to the field in the table.. Expression can be a single field name,
or
many
fields and constants implied in an arithmetic expression, but the argument
is supplied as a string..



Hoping it may help,
Vanderghast, Access MVP

I have a form and subform for a student course booking system and
want
to
display the total spent for the student throught the year in the
main
part
of
the form.

In the sub form I enter the subsistance paid, the travelling and course
fee.
I total these in a calculated control to show the total cost for the
student
in that transaction.

How can I maintain a running total of total cost for each student, ie
display the total costs (spend to date) for each student over a
period
in
the
main form? I have tried using dsum to sum the individual values
in
the
course table but am having a problem witht the syntax. Please advise.
Thanks
in advance.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DSUM AND FORM UPDATE 7
DSum HELP Please 10
Subform Totals 4
Form/subform totals don't balance when subform empty 1
purpose of subform? 45
Total Detail on Continuous Form 2
TINA - totalling 5
Query Calc Slow to Update 5

Top