Click here to Skip to main content
15,073,911 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
This Code gives the correct result of the dataset ( access backend data base) if all evaluate to true or all evaluate to false.

However; If some are false and some are true then the answer seems
to pick up the .00 of the ones that are false and adds them to the Sum. I believe this to be a problem with the way it's evaluating the structure as i have it now. Any help would be appreciated. I am new to VB net and find the RDLC confusing when trying to construct and if then type condition.

Where I should expect $240.00 I end up with $240.75
Confirmed.value is Boolean and serviceCost is currency datatype.

What my objective is on this is to take the sum of service cost and then deduct those that evaluate to false to get the final result.

=FormatCurrency(Sum(str(Fields!ServiceCost.Value) -(str(Fields!ServiceCost.value and Fields!Confirmed.value=False))))
tiggerc 12-Jul-10 11:34am
do you want to do this in a query or in code, because it would be far easier to return the result from DB see answer below.

I agree with tiggerc that a SELECT statement should be used to return the data you want. Then you can keep it simple. I believe the code below does what you are looking for.

SELECT c.ID, Sum(t.Amount) AS SumOfAmount
FROM Customers c INNER JOIN Transactions t ON c.ID = t.CustID
WHERE (t.Confirmed)=True

Amount - Currency (Double)
Confirmed - Boolean
Vs 2008
What I have is an appointment book with serviceCost( currency) and confirmed ( boolean)

What I want to be able to do is to sum up all the serviceCost on the rdlc report to get the total. Then subtract the servicecost where it evaluates to False to get the true total. If something evaluates to false, it means that the service was not completed or the customer missed the appointment.

Not sure how to format it directly on the textfield for the RDLC.

Datatype ServiceCost is Currency and DataType for Confirmed is Boolean

So if I have
ServiceCost Confirmed
$45.50 Perm True
$30.00 Color True
$25.50 WaX False

I want to be able to get 75.50 But that is not what is happening.
I thought I could just get the totals where confirmed =true but
so far have not been able to get that without adding it up and then subtracting the servicecost where it evaluates to False.

Here is what I have tried, including custom code
=Code.SumBooleanAmts(Fields!Confirm.Value, Fields!ServiceCost.Value)

( for above i used
Public Dim ServiceCostTotal as Double
Public Function SumBooleanAmts(Byval Confirm as Boolean, Byval ServiceCost as Object) as Object
If Confirm = True then
ServiceCostTotal += ServiceCost
Return 0
End If
Return ServiceCostTotal
End Function )

'=Sum(Cdbl(Fields!ServiceCost.Value))-(((sum((Cdbl(Fields!ServiceCost.value and Fields!Confirmed.value.tostring = "False"))))))
'=FormatCurrency(Sum(str(Fields!ServiceCost.Value) -(str(Fields!ServiceCost.value and Fields!Confirmed.value=False))))
'=FormatCurrency(Sum(str(Fields!ServiceCost.Value) -(Str(Fields!ServiceCost.value and CBool(Fields!Confirmed.value=False)))))
'=FormatCurrency(Sum(Fields!ServiceCost.Value and Fields!Confirmed.value=True))
'=FormatCurrency(Sum(str(Fields!ServiceCost.Value) -(str(Fields!ServiceCost.value and Fields!Confirmed.value=False))))
'=IIF(Fields!Confirmed.Value = "False",0,0 and Sum(Fields!ServiceCost.value))
'=Sum(IIF(Fields!ServiceCost.value and Fields!Confirmed.Value="False",0,1))
'=sum(CDec(Fields!ServiceCost.Value and Fields!Confirmed.value=False)))
'=FormatCurrency(Sum(fix(Fields!ServiceCost.Value and Fields!Confirmed.value=False)))
'=IIf (Fields!Confirmed.value ,0,1 and sum(Fields!ServiceCost.Value))Sum(Fields!ServiceCost.Value, "shx_DS_appointmentReports_Appointments")
'=FormatCurrency(Sum(str(Fields!ServiceCost.Value) -(str(Fields!ServiceCost.value and Fields!Confirmed.value=False))))
'=FormatCurrency(Sum(CDec(Fields!ServiceCost.Value and Fields!Confirmed.value=False)))
'=Sum(IIF(Fields!Confirmed.Value = "False", 1, 0))
'=Sum(IIF(Fields!ServiceCost.value and Fields!Confirmed.Value,0,0))
'=IIF(Fields!Confirmed.Value = "False", 0, 0 and Sum(Fields!ServiceCost.value))
'=FormatCurrency(Sum(Fields!ServiceCost.Value and Fields!Confirmed.value=False))
The expression for the format must be this way for the RDLC report

=FormatCurrency(Sum(str(Fields!ServiceCost.Value) -(str(Fields!ServiceCost.value and Fields!Confirmed.value=False)))

It always starts with the = sign..

I do know how to create an if then condtional statement but this is not what is needed here.
Sandeep Mewara 12-Jul-10 14:47pm
Reason for my vote of 1
Either use 'Improve Question' link to update your question OR use 'Add comment' feature to respond to an answer. Dont hit answer button when actually it is not an answer.
tiggerc 12-Jul-10 17:21pm
What version of vis studio are you using? and surely if you are subtracting the fields that are false do you not need to sum all of the fields that are false,
looking at your formula what your are saying is format this into currency, Sum( str( Field!ServiceCost)) - (str(Fields!ServiceCost and Fields!Confirmed) ) this isn't logical buddy.
what type of field is servicecost in your access database, ? more code needed.
SELECT SUM( yourfield ) FROM yourtable WHERE yourvalue = 1
mindserve 12-Jul-10 12:52pm
=FormatCurrency(Sum(str(Fields!ServiceCost.Value) -(str(Fields!ServiceCost.value and Fields!Confirmed.value=False)))

It has to be formatted as above for the RDLC report. YOur select SUM does not work on the textfield in the RDLC report.
mindserve 13-Jul-10 13:56pm
Reason for my vote of 5
ok, accepted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900