Form Total

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Fitness club database - I have a (continuous) subform bound to a main form.
Main form is customer name and details and subform is his monthly membership
fees.
So i have an owing field , amount paid field and then a calculated field
working out the outstanding amount i.e Amount owing - Amount paid

then in the form footer of the subform i want to know the amount outstanding
from all the months so i did Sum ([Owing]) - Sum ([Amount]). I tried all
sorts of ways Sum( ([Owing]) - ([Amount])) but whatever i do i get this
#error - what am i doing wrong.!!
 
Just one question first, did you put an equal sign before the sum
=Sum ([Owing]) - Sum ([Amount])
 
Yes i did what else could be the problem?

Ofer said:
Just one question first, did you put an equal sign before the sum
=Sum ([Owing]) - Sum ([Amount])


Database User said:
Fitness club database - I have a (continuous) subform bound to a main form.
Main form is customer name and details and subform is his monthly membership
fees.
So i have an owing field , amount paid field and then a calculated field
working out the outstanding amount i.e Amount owing - Amount paid

then in the form footer of the subform i want to know the amount outstanding
from all the months so i did Sum ([Owing]) - Sum ([Amount]). I tried all
sorts of ways Sum( ([Owing]) - ([Amount])) but whatever i do i get this
#error - what am i doing wrong.!!
Click to expand...
Click to expand...
 
I can think of three more things
1. The fields Owing and Amount are they the name of the fields in the table
and not in the form?
2. The above fields are thys calculated fields?
3. Are there any Null values, if so add the NZ function
=Sum (Nz([Owing],0)) - Sum (NZ([Amount],0))

--
I hope that helped
Good Luck


Database User said:
Yes i did what else could be the problem?

Ofer said:
Just one question first, did you put an equal sign before the sum
=Sum ([Owing]) - Sum ([Amount])


Database User said:
Fitness club database - I have a (continuous) subform bound to a main form.
Main form is customer name and details and subform is his monthly membership
fees.
So i have an owing field , amount paid field and then a calculated field
working out the outstanding amount i.e Amount owing - Amount paid

then in the form footer of the subform i want to know the amount outstanding
from all the months so i did Sum ([Owing]) - Sum ([Amount]). I tried all
sorts of ways Sum( ([Owing]) - ([Amount])) but whatever i do i get this
#error - what am i doing wrong.!!
Click to expand...
Click to expand...
Click to expand...
 
I'll explain exactly:

ITs a fitness centre gym so i have an Mcost field that is how much he owes
but only if he's an active member so the Owing field is a calcualated field
that says

=iif(([active member]) = no, "0",([Mcost]))

Then the AmountPaid field is a regular field from the query based on a few
tables

Then the AmountOutstanding field is a calculated field that says ([Owing]) -
([AmountPaid])

All that works fine just in the form footer when i want to do Sum([Owing]) -
Sum ([Amount Paid]) or i tried Sum([oustanding]) it just gives me errors

Are the calculated fields giving me problems.

I tried what you wrote about nz function but it didnt help

Database User said:
Yes i did what else could be the problem?

Ofer said:
Just one question first, did you put an equal sign before the sum
=Sum ([Owing]) - Sum ([Amount])


Database User said:
Fitness club database - I have a (continuous) subform bound to a main form.
Main form is customer name and details and subform is his monthly membership
fees.
So i have an owing field , amount paid field and then a calculated field
working out the outstanding amount i.e Amount owing - Amount paid

then in the form footer of the subform i want to know the amount outstanding
from all the months so i did Sum ([Owing]) - Sum ([Amount]). I tried all
sorts of ways Sum( ([Owing]) - ([Amount])) but whatever i do i get this
#error - what am i doing wrong.!!
Click to expand...
Click to expand...
Click to expand...
 
You can't sum on calculated field.
If you have a field in the details section, that has that in the control
source
=iif([field1] = False,0,[Field2])
Then you can't sum on that field that you created, the sum should be, on the
calculateion it self.
=Sum(iif([field1] = False,0,[Field2]))

--
I hope that helped
Good Luck


Database User said:
I'll explain exactly:

ITs a fitness centre gym so i have an Mcost field that is how much he owes
but only if he's an active member so the Owing field is a calcualated field
that says

=iif(([active member]) = no, "0",([Mcost]))

Then the AmountPaid field is a regular field from the query based on a few
tables

Then the AmountOutstanding field is a calculated field that says ([Owing]) -
([AmountPaid])

All that works fine just in the form footer when i want to do Sum([Owing]) -
Sum ([Amount Paid]) or i tried Sum([oustanding]) it just gives me errors

Are the calculated fields giving me problems.

I tried what you wrote about nz function but it didnt help

Database User said:
Yes i did what else could be the problem?

Ofer said:
Just one question first, did you put an equal sign before the sum
=Sum ([Owing]) - Sum ([Amount])


:

Fitness club database - I have a (continuous) subform bound to a main form.
Main form is customer name and details and subform is his monthly membership
fees.
So i have an owing field , amount paid field and then a calculated field
working out the outstanding amount i.e Amount owing - Amount paid

then in the form footer of the subform i want to know the amount outstanding
from all the months so i did Sum ([Owing]) - Sum ([Amount]). I tried all
sorts of ways Sum( ([Owing]) - ([Amount])) but whatever i do i get this
#error - what am i doing wrong.!!
Click to expand...
Click to expand...
Click to expand...
 
thanks for your help that did it!!

Ofer said:
You can't sum on calculated field.
If you have a field in the details section, that has that in the control
source
=iif([field1] = False,0,[Field2])
Then you can't sum on that field that you created, the sum should be, on the
calculateion it self.
=Sum(iif([field1] = False,0,[Field2]))

--
I hope that helped
Good Luck


Database User said:
I'll explain exactly:

ITs a fitness centre gym so i have an Mcost field that is how much he owes
but only if he's an active member so the Owing field is a calcualated field
that says

=iif(([active member]) = no, "0",([Mcost]))

Then the AmountPaid field is a regular field from the query based on a few
tables

Then the AmountOutstanding field is a calculated field that says ([Owing]) -
([AmountPaid])

All that works fine just in the form footer when i want to do Sum([Owing]) -
Sum ([Amount Paid]) or i tried Sum([oustanding]) it just gives me errors

Are the calculated fields giving me problems.

I tried what you wrote about nz function but it didnt help

Database User said:
Yes i did what else could be the problem?

:

Just one question first, did you put an equal sign before the sum
=Sum ([Owing]) - Sum ([Amount])


:

Fitness club database - I have a (continuous) subform bound to a main form.
Main form is customer name and details and subform is his monthly membership
fees.
So i have an owing field , amount paid field and then a calculated field
working out the outstanding amount i.e Amount owing - Amount paid

then in the form footer of the subform i want to know the amount outstanding
from all the months so i did Sum ([Owing]) - Sum ([Amount]). I tried all
sorts of ways Sum( ([Owing]) - ([Amount])) but whatever i do i get this
#error - what am i doing wrong.!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top