Dsum or other method to calculate running total spend.

[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.

 

[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

 

[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

 

[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] & " ' ")

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])

 

[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.

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

 

[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.

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] & " ' ")

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

 

[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

 

[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

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.

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] & " ' ")

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.

 

[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

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.

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] & " ' ")

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.