Error Message on Calculation

  • Thread starter Thread starter Sherry
  • Start date Start date
S

Sherry

Thanks Julie, but that's what I've been trying to do with
not good results. The field on the subform that I am
trying to sum is the "Total" field. This field itself is a
calculated field from 2 other fields (Cost and Tax added
together). In the footer of the subform I added the
following control: =sum ( [Total] ) . This returns an
error. Can you see what I might be doing wrong?

I thought I would probably have to make the calculation on
the subform then link it to the main form, but I can't
seem to get past getting the calc correct on the subform.

Thanks for any help you can offer!
Sherry
 
Hi Sherry

it's easier on everyone if you keep your replies in your original thread ...

the problem is that in the footer of the subform you need to repeat the
calculation that gives you total not just reference total

try
=sum([cost]+[tax])

Cheers
JulieD
 
Using =sum ( [cost]+[tax]) does not work either. Sigh.
About to pull my hair out here. I am wondering if it has
anything to do with the fact that the Tax field is a
formula in itself, in that, I have a Yes/No field
("Taxable?") that I have written an IF statement in the
Tax field referencing that answer. The Statement reads as
follows: "IIf ([Taxable?]=Yes, [Cost]*.07, 0), saying
that if the box for Yes is checked, it will calculate the
tax and put the amount in the tax field, but if it is not
checked, it will leave the tax field 0.

Then I have the Total field which adds together the Cost
and Tax fields. "[Cost]+[Tax].

I tried summing both [Total] and [Cost]+[Tax] with the
same results. I am using Access 2002, if that makes any
difference.

Thanks,
Sherry
-----Original Message-----
Hi Sherry

it's easier on everyone if you keep your replies in your original thread ...

the problem is that in the footer of the subform you need to repeat the
calculation that gives you total not just reference total

try
=sum([cost]+[tax])

Cheers
JulieD

Sherry said:
Thanks Julie, but that's what I've been trying to do with
not good results. The field on the subform that I am
trying to sum is the "Total" field. This field itself is a
calculated field from 2 other fields (Cost and Tax added
together). In the footer of the subform I added the
following control: =sum ( [Total] ) . This returns an
error. Can you see what I might be doing wrong?

I thought I would probably have to make the calculation on
the subform then link it to the main form, but I can't
seem to get past getting the calc correct on the subform.

Thanks for any help you can offer!
Sherry
Click to expand...


.
Click to expand...
 
Hi Sherry

have you got spaces in your =sum([cost]+[tax]) fields?
is the default value of the tax field 0 or null?

Cheers
Julie

Sherry said:
Using =sum ( [cost]+[tax]) does not work either. Sigh.
About to pull my hair out here. I am wondering if it has
anything to do with the fact that the Tax field is a
formula in itself, in that, I have a Yes/No field
("Taxable?") that I have written an IF statement in the
Tax field referencing that answer. The Statement reads as
follows: "IIf ([Taxable?]=Yes, [Cost]*.07, 0), saying
that if the box for Yes is checked, it will calculate the
tax and put the amount in the tax field, but if it is not
checked, it will leave the tax field 0.

Then I have the Total field which adds together the Cost
and Tax fields. "[Cost]+[Tax].

I tried summing both [Total] and [Cost]+[Tax] with the
same results. I am using Access 2002, if that makes any
difference.

Thanks,
Sherry
-----Original Message-----
Hi Sherry

it's easier on everyone if you keep your replies in your original thread ...

the problem is that in the footer of the subform you need to repeat the
calculation that gives you total not just reference total

try
=sum([cost]+[tax])

Cheers
JulieD

Sherry said:
Thanks Julie, but that's what I've been trying to do with
not good results. The field on the subform that I am
trying to sum is the "Total" field. This field itself is a
calculated field from 2 other fields (Cost and Tax added
together). In the footer of the subform I added the
following control: =sum ( [Total] ) . This returns an
error. Can you see what I might be doing wrong?

I thought I would probably have to make the calculation on
the subform then link it to the main form, but I can't
seem to get past getting the calc correct on the subform.

Thanks for any help you can offer!
Sherry
Click to expand...


.
Click to expand...
Click to expand...
 
Hi Sherry

I think the problem might be that Tax is a calculated field based on an IIF
statement ..
i'm not sure of the best way around this - personally i'ld be tempted to
store Tax in the underlying table ... but there's others on this ng far more
qualified to comment on this matter

so i think i'll just stand aside ...

good luck with the DB
Cheers
JulieD


JulieD said:
Hi Sherry

have you got spaces in your =sum([cost]+[tax]) fields?
is the default value of the tax field 0 or null?

Cheers
Julie

Sherry said:
Using =sum ( [cost]+[tax]) does not work either. Sigh.
About to pull my hair out here. I am wondering if it has
anything to do with the fact that the Tax field is a
formula in itself, in that, I have a Yes/No field
("Taxable?") that I have written an IF statement in the
Tax field referencing that answer. The Statement reads as
follows: "IIf ([Taxable?]=Yes, [Cost]*.07, 0), saying
that if the box for Yes is checked, it will calculate the
tax and put the amount in the tax field, but if it is not
checked, it will leave the tax field 0.

Then I have the Total field which adds together the Cost
and Tax fields. "[Cost]+[Tax].

I tried summing both [Total] and [Cost]+[Tax] with the
same results. I am using Access 2002, if that makes any
difference.

Thanks,
Sherry
-----Original Message-----
Hi Sherry

it's easier on everyone if you keep your replies in your original thread ...

the problem is that in the footer of the subform you need to repeat the
calculation that gives you total not just reference total

try
=sum([cost]+[tax])

Cheers
JulieD

Thanks Julie, but that's what I've been trying to do with
not good results. The field on the subform that I am
trying to sum is the "Total" field. This field itself is a
calculated field from 2 other fields (Cost and Tax added
together). In the footer of the subform I added the
following control: =sum ( [Total] ) . This returns an
error. Can you see what I might be doing wrong?

I thought I would probably have to make the calculation on
the subform then link it to the main form, but I can't
seem to get past getting the calc correct on the subform.

Thanks for any help you can offer!
Sherry


.
Click to expand...
Click to expand...
Click to expand...
 
Hi Julie,

I can't thank you enough for all your help! Believe it or
not, I finally got it to work by putting this into the
footer of the subform:

=Sum(IIf([Taxable Item?]=Yes,[Cost]*0.07,0)+[Cost])


Then in the main form I reference to that by virtue of
this:

=CHARGES.Form![Total Amount]

Works great now, but I had to really put on my thinking
cap to come to the conclusion that I did, and I couldn't
have done that without your help!

Stay tuned for future whining! LOL!

Thanks again!
Sherry

-----Original Message-----
Hi Sherry

have you got spaces in your =sum([cost]+[tax]) fields?
is the default value of the tax field 0 or null?

Cheers
Julie

Sherry said:
Using =sum ( [cost]+[tax]) does not work either. Sigh.
About to pull my hair out here. I am wondering if it has
anything to do with the fact that the Tax field is a
formula in itself, in that, I have a Yes/No field
("Taxable?") that I have written an IF statement in the
Tax field referencing that answer. The Statement reads as
follows: "IIf ([Taxable?]=Yes, [Cost]*.07, 0), saying
that if the box for Yes is checked, it will calculate the
tax and put the amount in the tax field, but if it is not
checked, it will leave the tax field 0.

Then I have the Total field which adds together the Cost
and Tax fields. "[Cost]+[Tax].

I tried summing both [Total] and [Cost]+[Tax] with the
same results. I am using Access 2002, if that makes any
difference.

Thanks,
Sherry
-----Original Message-----
Hi Sherry

it's easier on everyone if you keep your replies in
Click to expand...
your
original thread ...
the problem is that in the footer of the subform you
Click to expand...
need
to repeat the
calculation that gives you total not just reference total

try
=sum([cost]+[tax])

Cheers
JulieD

Thanks Julie, but that's what I've been trying to do with
not good results. The field on the subform that I am
trying to sum is the "Total" field. This field itself is a
calculated field from 2 other fields (Cost and Tax added
together). In the footer of the subform I added the
following control: =sum ( [Total] ) . This returns an
error. Can you see what I might be doing wrong?

I thought I would probably have to make the
Click to expand...
calculation
on
the subform then link it to the main form, but I can't
seem to get past getting the calc correct on the subform.

Thanks for any help you can offer!
Sherry


.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi Sherry

you won't believe this, i actually tried your solution, but i couldn't get
it to work ... maybe it was just too late at night for me :)

Glad you solved that problem .. onwards & upwards :)

Cheers
JulieD


Hi Julie,

I can't thank you enough for all your help! Believe it or
not, I finally got it to work by putting this into the
footer of the subform:

=Sum(IIf([Taxable Item?]=Yes,[Cost]*0.07,0)+[Cost])


Then in the main form I reference to that by virtue of
this:

=CHARGES.Form![Total Amount]

Works great now, but I had to really put on my thinking
cap to come to the conclusion that I did, and I couldn't
have done that without your help!

Stay tuned for future whining! LOL!

Thanks again!
Sherry

-----Original Message-----
Hi Sherry

have you got spaces in your =sum([cost]+[tax]) fields?
is the default value of the tax field 0 or null?

Cheers
Julie

Sherry said:
Using =sum ( [cost]+[tax]) does not work either. Sigh.
About to pull my hair out here. I am wondering if it has
anything to do with the fact that the Tax field is a
formula in itself, in that, I have a Yes/No field
("Taxable?") that I have written an IF statement in the
Tax field referencing that answer. The Statement reads as
follows: "IIf ([Taxable?]=Yes, [Cost]*.07, 0), saying
that if the box for Yes is checked, it will calculate the
tax and put the amount in the tax field, but if it is not
checked, it will leave the tax field 0.

Then I have the Total field which adds together the Cost
and Tax fields. "[Cost]+[Tax].

I tried summing both [Total] and [Cost]+[Tax] with the
same results. I am using Access 2002, if that makes any
difference.

Thanks,
Sherry
-----Original Message-----
Hi Sherry

it's easier on everyone if you keep your replies in your
original thread ...

the problem is that in the footer of the subform you need
to repeat the
calculation that gives you total not just reference total

try
=sum([cost]+[tax])

Cheers
JulieD

Thanks Julie, but that's what I've been trying to do
with
not good results. The field on the subform that I am
trying to sum is the "Total" field. This field itself
is a
calculated field from 2 other fields (Cost and Tax added
together). In the footer of the subform I added the
following control: =sum ( [Total] ) . This returns an
error. Can you see what I might be doing wrong?

I thought I would probably have to make the calculation
on
the subform then link it to the main form, but I can't
seem to get past getting the calc correct on the
subform.

Thanks for any help you can offer!
Sherry


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top