adding dsum results in a query

G

Guest

My query has the following fields and expressions but the A+B field is
concatenating $1,577.74$0.00 instead of adding them. Any suggestions.

Total Principal SLN Balance:
FormatCurrency(nz(DSum("[Initial Proceeds ]","[T1","[Trade TypeID
]='SLN'"),0),2)
Total Investment Principal Balance (A):
FormatCurrency(nz(DSum("[Initial Proceeds ]","[T1]","[Trade TypeID
]<>'SLN'"),0),2)
Total Designated Payment Obligations Principal Balance (B):
FormatCurrency(nz((SELECT Sum([T2].[NET_OS])
FROM [T2]),0),2)
Total of (A) + (B):
[Total Investment Principal Balance (A)]+[Total Designated Payment
Obligations Principal Balance (B)]
Result:
IIf([Total of (A) + (B)]>=[Total Principal SLN Balance],"PASS","FAIL")
 
G

Guest

The problem is, the FormatCurrency return string and not number
So, String1 + String2 will return String1String2 connected

Try to use the FormatCurrency function on the final resault, instead of
using it on the original resault, something like

Total Principal SLN Balance:
nz(DSum("[Initial Proceeds ]","[T1","[Trade TypeID ]='SLN'"),0)
Total Investment Principal Balance (A):
nz(DSum("[Initial Proceeds ]","[T1]","[Trade TypeID ]<>'SLN'"),0)
Total Designated Payment Obligations Principal Balance (B):
nz((SELECT Sum([T2].[NET_OS])
FROM [T2]),0)
Total of (A) + (B):
FormatCurrency([Total Investment Principal Balance (A)]+[Total Designated
Payment Obligations Principal Balance (B)],2)
Result:
IIf([Total of (A) + (B)]>=[Total Principal SLN Balance],"PASS","FAIL")
 
G

Guest

Thanks, that makes sense but I took them out and have the same problem. It
looks like the field w/ the select statement, when I ran that field w/o
values the nz zero was not concatenated. How can I turn the select result
into a number.
I tried the formatnumber() but that looks like a str as well.

Total Designated Payment Obligations Principal Balance (B): nz((SELECT
Sum([Participation Data].[NET_OS])
FROM [Participation Data]),0)

Ofer Cohen said:
The problem is, the FormatCurrency return string and not number
So, String1 + String2 will return String1String2 connected

Try to use the FormatCurrency function on the final resault, instead of
using it on the original resault, something like

Total Principal SLN Balance:
nz(DSum("[Initial Proceeds ]","[T1","[Trade TypeID ]='SLN'"),0)
Total Investment Principal Balance (A):
nz(DSum("[Initial Proceeds ]","[T1]","[Trade TypeID ]<>'SLN'"),0)
Total Designated Payment Obligations Principal Balance (B):
nz((SELECT Sum([T2].[NET_OS])
FROM [T2]),0)
Total of (A) + (B):
FormatCurrency([Total Investment Principal Balance (A)]+[Total Designated
Payment Obligations Principal Balance (B)],2)
Result:
IIf([Total of (A) + (B)]>=[Total Principal SLN Balance],"PASS","FAIL")



--
Good Luck
BS"D


Frank said:
My query has the following fields and expressions but the A+B field is
concatenating $1,577.74$0.00 instead of adding them. Any suggestions.

Total Principal SLN Balance:
FormatCurrency(nz(DSum("[Initial Proceeds ]","[T1","[Trade TypeID
]='SLN'"),0),2)
Total Investment Principal Balance (A):
FormatCurrency(nz(DSum("[Initial Proceeds ]","[T1]","[Trade TypeID
]<>'SLN'"),0),2)
Total Designated Payment Obligations Principal Balance (B):
FormatCurrency(nz((SELECT Sum([T2].[NET_OS])
FROM [T2]),0),2)
Total of (A) + (B):
[Total Investment Principal Balance (A)]+[Total Designated Payment
Obligations Principal Balance (B)]
Result:
IIf([Total of (A) + (B)]>=[Total Principal SLN Balance],"PASS","FAIL")
Click to expand...
Click to expand...
 
G

Guest

You can use the functions

CDbl([FieldName])

to change the field type to double
--
Good Luck
BS"D


Frank said:
Thanks, that makes sense but I took them out and have the same problem. It
looks like the field w/ the select statement, when I ran that field w/o
values the nz zero was not concatenated. How can I turn the select result
into a number.
I tried the formatnumber() but that looks like a str as well.

Total Designated Payment Obligations Principal Balance (B): nz((SELECT
Sum([Participation Data].[NET_OS])
FROM [Participation Data]),0)

Ofer Cohen said:
The problem is, the FormatCurrency return string and not number
So, String1 + String2 will return String1String2 connected

Try to use the FormatCurrency function on the final resault, instead of
using it on the original resault, something like

Total Principal SLN Balance:
nz(DSum("[Initial Proceeds ]","[T1","[Trade TypeID ]='SLN'"),0)
Total Investment Principal Balance (A):
nz(DSum("[Initial Proceeds ]","[T1]","[Trade TypeID ]<>'SLN'"),0)
Total Designated Payment Obligations Principal Balance (B):
nz((SELECT Sum([T2].[NET_OS])
FROM [T2]),0)
Total of (A) + (B):
FormatCurrency([Total Investment Principal Balance (A)]+[Total Designated
Payment Obligations Principal Balance (B)],2)
Result:
IIf([Total of (A) + (B)]>=[Total Principal SLN Balance],"PASS","FAIL")



--
Good Luck
BS"D


Frank said:
My query has the following fields and expressions but the A+B field is
concatenating $1,577.74$0.00 instead of adding them. Any suggestions.

Total Principal SLN Balance:
FormatCurrency(nz(DSum("[Initial Proceeds ]","[T1","[Trade TypeID
]='SLN'"),0),2)
Total Investment Principal Balance (A):
FormatCurrency(nz(DSum("[Initial Proceeds ]","[T1]","[Trade TypeID
]<>'SLN'"),0),2)
Total Designated Payment Obligations Principal Balance (B):
FormatCurrency(nz((SELECT Sum([T2].[NET_OS])
FROM [T2]),0),2)
Total of (A) + (B):
[Total Investment Principal Balance (A)]+[Total Designated Payment
Obligations Principal Balance (B)]
Result:
IIf([Total of (A) + (B)]>=[Total Principal SLN Balance],"PASS","FAIL")
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query to calc running total 1
adding a row with empty results in a query 6
DSum update query with multiple fields 0
Running total in query 2
Monthly transactions in query 3
How to get a running balance total 6
Assigning a query to a new report in VBA 4
returning a multi-criteria sum from a table to a query field 1

Top