Total Query from Subtotal Query

[phaddock4]

Please help.

GOAL: I hope to calculate a total amount in a TotalQuery (or field in
the SubQuery?), based upon three field amounts in a SubQuery.

STATUS: When I create an expression in the TotalQuery based upon
SubQuery fields, e.g:

Total: [SOPAmount] + [PetFilingAmount] + [SummonsFilingAm ount]

'The resulting [Total] field displays each of the three SubQuery
amounts sequentially in the same field [156.00 50.00 5.00], rather than
the sum of having added them. However, when I test the expression
using multiplication (*) the [Total] field displays the correct total
amount. For example, the expression:

Total: ([SOPAmount]*[PetFilingAmount])+ [SummonsFilingAm ount]
'yields a correct single total amount, [7505.00].

The SubQuery in ms-access 2k sums the amounts from certain checks
entered for specific purposes. All resulting records from the Subquery
are sorted on a [MergeDate] field:

The Subquery SQL statement is as follows:

SELECT FormatCurrency( DSum("[Amount]","tblPaymentAc counting","[Purpose]
= 'Service of Process' "),2) AS SOPAmount,
FormatCurrency( DSum("[Amount]","tblPaymentAc counting","[Purpose] =
'Petition Filing Fees' "),2) AS PetFilingAmount ,
FormatCurrency( DSum("[Amount]","tblPaymentAc counting","[Purpose] =
'Summons Filing Fees' "),2) AS SummonsFilingAm ount
FROM tblDebtorMaster FileList
WHERE (((tblDebtorMas terFileList.Mer geDate)=#1/14/2005#));

I don't work with Access all the time, so I am not that astute at these
queries. Could someone please advise me on the best method to obtain a
TotalFees based upon the added total from the three field amounts in
the underlying query.

[David Seeto via AccessMonster.com]

I suspect your problem may be that the FormatCurrency has turned the fields into a text field by default, so that the + operator acts as a concantenation rather than an addition. In your total query, try explcitly converting them back into a number eg:

TotalFees: CDbl([SOPAmount]) + Cdbl([PetFilingAmount]) + CDbl([SummonsFilingAm ount])

(I think it's CDbl - it's whatever the equivalent of CInt and CStr are... CSng may be enough too).

--
Message posted via http://www.accessmonster.com

[phaddock4]

David Seeto via AccessMonster.c om wrote:

I suspect your problem may be that the FormatCurrency has turned the fields into a text field by default, so that the + operator acts as a
concantenation rather than an addition. In your total query, try
explcitly converting them back into a number eg:
TotalFees: CDbl([SOPAmount]) + Cdbl([PetFilingAmount]) + CDbl([SummonsFilingAm ount])
(I think it's CDbl - it's whatever the equivalent of CInt and CStr are... CSng may be enough too).
--
Message posted via http://www.accessmonster.com

Yes, you were correct about the FormatCurrency conversion being the
problem. Your suggestion fixed the problem, and I appreciate your
response. Thanks.