I WANT TO ADD A CALCULATED CONTROL TO CALCULATE TOTALS ON EACH SU.

[Guest]

I have a form and a sub form linked on a one to many relationship. I want
to add add some calculated fields which will calculate for totals for each
new entry on the sub form for the person in the main form. I.E. when I
input a new form for a person I want to be able to calculate the totals
entered for each column in the subform and display them under column where
the input values have been entered. I have tried using the syntax below
without success

=Sum([COUR_ACT_FEE]+[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]+[COUR_ACT_EXPENSES])


Please help, I am completly lost on this one! Thanks in advance.

 

[Allen Browne]

The Sum() is not needed, but you may need Nz() to supply a zero if there is
no value for any of the arguments. Also a good idea to force the result to
Currency.

Try:
=CCur(Nz([COUR_ACT_FEE],0) + Nz([COUR_ACT_TRAVEL],0)
+Nz([COUR_ACT_SUBSISTANCE],0) + Nz(COUR_ACT_EXPENSES],0))

You might find it easier to create a query, and type that into the Field
row, so that Access shows you the total for each row.

 

[Guest]

Hi. Thanks for your reply. I tried

=CCur(Nz([COUR_ACT_FEE],0) +
Nz([COUR_ACT_TRAVEL],0)+Nz([COUR_ACT_SUBSISTANCE],0) +
Nz(COUR_ACT_EXPENSES],0))

but just got the message, the expression contains an invalid syntax.

On your other suggestion, I'm not sure I understand what you mean by
creating a query and typing that into the field. Do you mean, make a normal
query, then copy the syntax into the control source into the text field I am
want to use to display the column total?

The Sum() is not needed, but you may need Nz() to supply a zero if there is
no value for any of the arguments. Also a good idea to force the result to
Currency.

Try:
=CCur(Nz([COUR_ACT_FEE],0) + Nz([COUR_ACT_TRAVEL],0)
+Nz([COUR_ACT_SUBSISTANCE],0) + Nz(COUR_ACT_EXPENSES],0))

You might find it easier to create a query, and type that into the Field
row, so that Access shows you the total for each row.

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

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

I have a form and a sub form linked on a one to many relationship. I want
to add add some calculated fields which will calculate for totals for
each
new entry on the sub form for the person in the main form. I.E. when I
input a new form for a person I want to be able to calculate the totals
entered for each column in the subform and display them under column where
the input values have been entered. I have tried using the syntax below
without success

=Sum([COUR_ACT_FEE]+[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]+[COUR_ACT_EXPENSES])


Please help, I am completly lost on this one! Thanks in advance.

 

[Allen Browne]

If the full expression fails, then build it a bit at a time.

Make sure the Name of this text box is not the same as any of the fields in
the form's RecordSource. For example, it must not be called COUR_ACT_FEE.
Access gets confused if the control's Name is the same as a field, but it is
bound to something different.

Then try:
=Nz([COUR_ACT_FEE],0)
When you get that working, try:
=Nz([COUR_ACT_FEE],0) + Nz([COUR_ACT_TRAVEL],0)
and so on.

Yes, the alternative is to construct a normal query into your table, and
then type the entire expression into a fresh column in the Field row. Access
will run the calculation, and show it when you view the query. Then if you
use this query as the RecordSource for your form, you can refer to the
calculated field just like any other field.

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

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

Hi. Thanks for your reply. I tried

=CCur(Nz([COUR_ACT_FEE],0) +
Nz([COUR_ACT_TRAVEL],0)+Nz([COUR_ACT_SUBSISTANCE],0) +
Nz(COUR_ACT_EXPENSES],0))

but just got the message, the expression contains an invalid syntax.

On your other suggestion, I'm not sure I understand what you mean by
creating a query and typing that into the field. Do you mean, make a
normal
query, then copy the syntax into the control source into the text field I
am
want to use to display the column total?

The Sum() is not needed, but you may need Nz() to supply a zero if there
is
no value for any of the arguments. Also a good idea to force the result
to
Currency.

Try:
=CCur(Nz([COUR_ACT_FEE],0) + Nz([COUR_ACT_TRAVEL],0)
+Nz([COUR_ACT_SUBSISTANCE],0) + Nz(COUR_ACT_EXPENSES],0))

You might find it easier to create a query, and type that into the Field
row, so that Access shows you the total for each row.

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

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

I have a form and a sub form linked on a one to many relationship. I
want
to add add some calculated fields which will calculate for totals for
each
new entry on the sub form for the person in the main form. I.E. when
I
input a new form for a person I want to be able to calculate the
totals
entered for each column in the subform and display them under column
where
the input values have been entered. I have tried using the syntax
below
without success

=Sum([COUR_ACT_FEE]+[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]+[COUR_ACT_EXPENSES])


Please help, I am completly lost on this one! Thanks in advance.

 

[Guest]

It works!! Many thanks. Sorry about the delayed reply I've been out of
action for a couple of days! I did not know the form wizard truncates long
field names when it creates the form. When I realised this, I was able to
make things work as you suggested. Once again many thanks for your help.
Peter

If the full expression fails, then build it a bit at a time.

Make sure the Name of this text box is not the same as any of the fields in
the form's RecordSource. For example, it must not be called COUR_ACT_FEE.
Access gets confused if the control's Name is the same as a field, but it is
bound to something different.

Then try:
=Nz([COUR_ACT_FEE],0)
When you get that working, try:
=Nz([COUR_ACT_FEE],0) + Nz([COUR_ACT_TRAVEL],0)
and so on.

Yes, the alternative is to construct a normal query into your table, and
then type the entire expression into a fresh column in the Field row. Access
will run the calculation, and show it when you view the query. Then if you
use this query as the RecordSource for your form, you can refer to the
calculated field just like any other field.

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

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

Hi. Thanks for your reply. I tried

=CCur(Nz([COUR_ACT_FEE],0) +
Nz([COUR_ACT_TRAVEL],0)+Nz([COUR_ACT_SUBSISTANCE],0) +
Nz(COUR_ACT_EXPENSES],0))

but just got the message, the expression contains an invalid syntax.

On your other suggestion, I'm not sure I understand what you mean by
creating a query and typing that into the field. Do you mean, make a
normal
query, then copy the syntax into the control source into the text field I
am
want to use to display the column total?

The Sum() is not needed, but you may need Nz() to supply a zero if there
is
no value for any of the arguments. Also a good idea to force the result
to
Currency.

Try:
=CCur(Nz([COUR_ACT_FEE],0) + Nz([COUR_ACT_TRAVEL],0)
+Nz([COUR_ACT_SUBSISTANCE],0) + Nz(COUR_ACT_EXPENSES],0))

You might find it easier to create a query, and type that into the Field
row, so that Access shows you the total for each row.

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

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

I have a form and a sub form linked on a one to many relationship. I
want
to add add some calculated fields which will calculate for totals for
each
new entry on the sub form for the person in the main form. I.E. when
I
input a new form for a person I want to be able to calculate the
totals
entered for each column in the subform and display them under column
where
the input values have been entered. I have tried using the syntax
below
without success

=Sum([COUR_ACT_FEE]+[COUR_ACT_TRAVEL]+[COUR_ACT_SUBSISTANCE]+[COUR_ACT_EXPENSES])


Please help, I am completly lost on this one! Thanks in advance.