Use of Parntheses in Expressions
in [Queries]
|
From: bbal20 on 6 Jun 2010 19:49 Hello, I am fairly new to Access and Excel. I am trying to write a formula/expression, but I think my problem is as simple as having the parenthese in the wrong place. The formula I have currently is as follows: Sum(((IIf([Mapping]![Pricing]="CPM",[TPImpressions]/1000*[Mapping]![Price],IIf([Mapping]![Pricing]="CPC",[TPClicks]*[Mapping]![Price],[TPConv]*[Mapping]![Price]))))*[Mapping]![Eye Engage Cost]))))) I basically want the SUM(IF part to be evaluated first and then multiply the result by [Mapping]![Eye Engage Cost] at the end. Currently, the formula above returns the value in the Sum(IF, but doesn't mulitply by the Eye Engage Cost. Also, can anyone reccomend a website or good reference materail that will help me understand how parentheses work in Nested Formulas other than knowing that the innner most parentheses is always evaluated first? -- Thank you for your help and support
From: KARL DEWEY on 6 Jun 2010 23:02 Try this -- Sum(IIf([Mapping].[Pricing]="CPM",[TPImpressions]/1000*[Mapping].[Price],IIf([Mapping].[Pricing]="CPC",[TPClicks]*[Mapping].[Price],[TPConv]*[Mapping].[Price])))*[Mapping].[Eye Engage Cost] -- Build a little, test a little. "bbal20" wrote: > Hello, > > I am fairly new to Access and Excel. I am trying to write a > formula/expression, but I think my problem is as simple as having the > parenthese in the wrong place. The formula I have currently is as follows: > > Sum(((IIf([Mapping]![Pricing]="CPM",[TPImpressions]/1000*[Mapping]![Price],IIf([Mapping]![Pricing]="CPC",[TPClicks]*[Mapping]![Price],[TPConv]*[Mapping]![Price]))))*[Mapping]![Eye Engage Cost]))))) > > I basically want the SUM(IF part to be evaluated first and then multiply the > result by [Mapping]![Eye Engage Cost] at the end. Currently, the formula > above returns the value in the Sum(IF, but doesn't mulitply by the Eye Engage > Cost. > > Also, can anyone reccomend a website or good reference materail that will > help me understand how parentheses work in Nested Formulas other than knowing > that the innner most parentheses is always evaluated first? > -- > Thank you for your help and support
From: bbal20 on 7 Jun 2010 01:19 The following gave me an error saying "You tried to execute a query that does not include the specified expression" -- Thank you for your help and support "KARL DEWEY" wrote: > Try this -- > Sum(IIf([Mapping].[Pricing]="CPM",[TPImpressions]/1000*[Mapping].[Price],IIf([Mapping].[Pricing]="CPC",[TPClicks]*[Mapping].[Price],[TPConv]*[Mapping].[Price])))*[Mapping].[Eye Engage Cost] > > -- > Build a little, test a little. > > > "bbal20" wrote: > > > Hello, > > > > I am fairly new to Access and Excel. I am trying to write a > > formula/expression, but I think my problem is as simple as having the > > parenthese in the wrong place. The formula I have currently is as follows: > > > > Sum(((IIf([Mapping]![Pricing]="CPM",[TPImpressions]/1000*[Mapping]![Price],IIf([Mapping]![Pricing]="CPC",[TPClicks]*[Mapping]![Price],[TPConv]*[Mapping]![Price]))))*[Mapping]![Eye Engage Cost]))))) > > > > I basically want the SUM(IF part to be evaluated first and then multiply the > > result by [Mapping]![Eye Engage Cost] at the end. Currently, the formula > > above returns the value in the Sum(IF, but doesn't mulitply by the Eye Engage > > Cost. > > > > Also, can anyone reccomend a website or good reference materail that will > > help me understand how parentheses work in Nested Formulas other than knowing > > that the innner most parentheses is always evaluated first? > > -- > > Thank you for your help and support
From: John Spencer on 7 Jun 2010 08:59 Breaking that all down and formatting it for readability, I think you could use the following expression. Sum( IIf( [Mapping]![Pricing]="CPM", [TPImpressions]/1000*[Mapping]![Price], IIf( [Mapping]![Pricing]="CPC", [TPClicks]*[Mapping]![Price], [TPConv]*[Mapping]![Price] ) ) *[Mapping]![Eye Engage Cost] ) If that errors, perhaps you could post the SQL statement of the query. (Menu - View: SQL, copy and paste. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County bbal20 wrote: > Hello, > > I am fairly new to Access and Excel. I am trying to write a > formula/expression, but I think my problem is as simple as having the > parenthese in the wrong place. The formula I have currently is as follows: > > Sum(((IIf([Mapping]![Pricing]="CPM",[TPImpressions]/1000*[Mapping]![Price],IIf([Mapping]![Pricing]="CPC",[TPClicks]*[Mapping]![Price],[TPConv]*[Mapping]![Price]))))*[Mapping]![Eye Engage Cost]))))) > > I basically want the SUM(IF part to be evaluated first and then multiply the > result by [Mapping]![Eye Engage Cost] at the end. Currently, the formula > above returns the value in the Sum(IF, but doesn't mulitply by the Eye Engage > Cost. > > Also, can anyone reccomend a website or good reference materail that will > help me understand how parentheses work in Nested Formulas other than knowing > that the innner most parentheses is always evaluated first?
|
Pages: 1 Prev: calculating average days Next: complex query to pull unique values |