Calculating a sub-total on my form

[Guest]

Hi there,

My situation is actually quite simple, but for some reason, I can't get my
sub-total calculation to work.

I have a continuous form (called: frmTransferDetails) with a field called
"Amount". This form is a sub-form of frmTransfers.

All I am trying to do is check a couple of conditions to see if I should
keep the amount the same, or multiply it by -1.

I'll try to lay out my formula as reader friendly as possible:

=Sum(IIf(([TransType]=2 And
DLookUp("[CreditAccount]","tblAccounts","[AccountID] =" &
[Forms]![frmTransfers]![cboFromAccountID])=0) Or

([TransType]=1 And DLookUp("[CreditAccount]","tblAccounts","[AccountID] =" &
[Forms]![frmTransfers]![cboFromAccountID])=-1),[amount],[amount]*-1))

I know it's kind of a beast to look at.

I've toyed around with this and when I take out the Sum() part of the
formula and tested it on individual records and it appears to work fine. The
criteria checks the correct data and gives me the appropriate value when I
select the record. However, when I put the sum() around the formula, I get
an #Error and can't figure out why.

Can someone lend some assistance?

Thank you!!

 

[Wayne Morgan]

I would recommend using the formula without the Sum() function to create a
calculated field in the query that is the RecordSource for the subform. You
could then use the Sum() function on this field. The reference to the
control on the parent form may need to be replaced, but I would need to see
the RecordSource of both forms to know for sure. Try it as it is first and
see what happens.

 

[Guest]

Thanks Wayne,

I tried that method and it worked.

I never really thought about doing that but it was a good suggestion. I can
just hide the field so no one can see it.

Thanks for the suggestion!

--
TT

I would recommend using the formula without the Sum() function to create a
calculated field in the query that is the RecordSource for the subform. You
could then use the Sum() function on this field. The reference to the
control on the parent form may need to be replaced, but I would need to see
the RecordSource of both forms to know for sure. Try it as it is first and
see what happens.

--
Wayne Morgan
MS Access MVP

Hi there,

My situation is actually quite simple, but for some reason, I can't get my
sub-total calculation to work.

I have a continuous form (called: frmTransferDetails) with a field called
"Amount". This form is a sub-form of frmTransfers.

All I am trying to do is check a couple of conditions to see if I should
keep the amount the same, or multiply it by -1.

I'll try to lay out my formula as reader friendly as possible:

=Sum(IIf(([TransType]=2 And
DLookUp("[CreditAccount]","tblAccounts","[AccountID] =" &
[Forms]![frmTransfers]![cboFromAccountID])=0) Or

([TransType]=1 And DLookUp("[CreditAccount]","tblAccounts","[AccountID] ="
&
[Forms]![frmTransfers]![cboFromAccountID])=-1),[amount],[amount]*-1))

I know it's kind of a beast to look at.

I've toyed around with this and when I take out the Sum() part of the
formula and tested it on individual records and it appears to work fine.
The
criteria checks the correct data and gives me the appropriate value when I
select the record. However, when I put the sum() around the formula, I
get
an #Error and can't figure out why.

Can someone lend some assistance?

Thank you!!