Solved Calculated field based on another field on report (1 Viewer)

estelleh

Member
Local time
Today, 12:42
Joined
Jul 16, 2021
Messages
56
Good morning :)

I am creating a report which lists outstanding jobs and their cost. The Cost value is dependent on a "CostPer" field which has a value of "Job", "Each" or "1000".

So the calc will be
Code:
If CostPer = Job Then
    TotalCost = Cost1 + Cost2
ElseIf CostPer = Each Then
    TotalCost = (Cost1 * Quantity) + Cost2
ElseIf CostPer = "1000" Then
    TotalCost = ((Cost1 * Quantity) / 1000) + Cost2
End If

How do I calculate the TotalCost field in the report? Query? VBA?

<<Feeling particularly dumb >>o_O

TIA
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:42
Joined
May 7, 2009
Messages
19,169
use query:

select project_id, sum(switch(Costper = "job", [cost1] + [cost2], Costper = "each", (cost1 * quantity)+cost2, true, ((cost1 * quantity) / 1000) + cost2)) as TotalCost from yourtable group by project_id;
 

estelleh

Member
Local time
Today, 12:42
Joined
Jul 16, 2021
Messages
56
use query:

select project_id, sum(switch(Costper = "job", [cost1] + [cost2], Costper = "each", (cost1 * quantity)+cost2, true, ((cost1 * quantity) / 1000) + cost2)) as TotalCost from yourtable group by project_id;
I put a Switch statement into my query as follows (I ignored the sum part for now just to get the switch statement working)

Code:
CalcCost: Switch([CostPer]=1,([PrintCost]+[OrigCost]+[OtherCost]),[CostPer]=2,(([PrintCost]*[Quantity])+[OrigCost]+[OtherCost]),[CostPer]=3,(([PrintCost]*[Quantity])/1000)+[OrigCost]+[OtherCost])

CostPer values: 1 being "per Job", 2 being "Each" and 3 being "per 1000"

When I run the query, I get a result of #Error in CalcCost.....

A sample of the Query results:

1636025048721.png

Cost
 

estelleh

Member
Local time
Today, 12:42
Joined
Jul 16, 2021
Messages
56
Sorted :)

Turns out the CostPer is a text not a number field.

Thanks for pointing me in the right direction!
 

Users who are viewing this thread

Top Bottom