willjx
asked on
MS ACCESS 2007 syntax for an IIF "or".... /THEN statement that works in COGNOS
Below isyntax porduces the correct info in COGNOS:
If ( [PM View prd].[DataTarget History].[Target] < [PM View prd].[Data].[Baseline] and [PM View prd].[Data].[Do we want to see an increase in this Measure?] = 0 or [PM View prd].[DataTarget History].[Target] > [PM View prd].[Data].[Baseline] and [PM View prd].[Data].[Do we want to see an increase in this Measure?] = 1 )
THEN ( [PM View prd].[DataTarget History].[Target] )
ELSE (
[PM View prd].[Data].[Baseline] - ([PM View prd].[Data].[Baseline] * .05 ) )
-------------------------- ---------- ---------- ---------
Below is syntax I currently have in ACCESS that almost does the job. However,it is missing the statement after the above COGNOS "or", and I have been unsuccessful at providing an acceptable syntax to make the report totally accurate:
TG: IIf([TmsrAmt]![Target]<[ba seline],II f([Do we want to see an increase in this Measure?]=False,[TmsrAmt]! [Target],[ baseline]- ([baseline ]*0.05)),[ baseline]- ([baseline ]*0.05))
If ( [PM View prd].[DataTarget History].[Target] < [PM View prd].[Data].[Baseline] and [PM View prd].[Data].[Do we want to see an increase in this Measure?] = 0 or [PM View prd].[DataTarget History].[Target] > [PM View prd].[Data].[Baseline] and [PM View prd].[Data].[Do we want to see an increase in this Measure?] = 1 )
THEN ( [PM View prd].[DataTarget History].[Target] )
ELSE (
[PM View prd].[Data].[Baseline] - ([PM View prd].[Data].[Baseline] * .05 ) )
--------------------------
Below is syntax I currently have in ACCESS that almost does the job. However,it is missing the statement after the above COGNOS "or", and I have been unsuccessful at providing an acceptable syntax to make the report totally accurate:
TG: IIf([TmsrAmt]![Target]<[ba
ASKER
Unsatisfactory solution
The following should work:
IIF([PM View prd].[DataTarget History].[Target] < [PM View prd].[Data].[Baseline] AND [PM View prd].[Data].[Do we want to see an increase in this Measure?] = 0, [PM View prd].[DataTarget History].[Target], IIF([PM View prd].[DataTarget History].[Target] > [PM View prd].[Data].[Baseline] AND [PM View prd].[Data].[Do we want to see an increase in this Measure?] = 1, [PM View prd].[DataTarget History].[Target], [PM View prd].[Data].[Baseline] - ([PM View prd].[Data].[Baseline] * .05)))
IIF([PM View prd].[DataTarget History].[Target] < [PM View prd].[Data].[Baseline] AND [PM View prd].[Data].[Do we want to see an increase in this Measure?] = 0, [PM View prd].[DataTarget History].[Target], IIF([PM View prd].[DataTarget History].[Target] > [PM View prd].[Data].[Baseline] AND [PM View prd].[Data].[Do we want to see an increase in this Measure?] = 1, [PM View prd].[DataTarget History].[Target], [PM View prd].[Data].[Baseline] - ([PM View prd].[Data].[Baseline] * .05)))
ASKER
Not quite.
Both IIF statements produce the same result. In My COGNOS statement noted above, you will notice an "OR" which somehow needs to be included.
Your sugestion reproduced in ACCESS systax below does the same as my above noted ACCESS syntax:
TG: IIf([TmsrAmt]![Target]<[ba seline] AND
[Do we want to see an increase in this Measure?]=0,
[TmsrAmt]![Target] ,
-------here i need:
{on the other hand}
{OR}
IIf([TmsrAmt]![Target]>[ba seline] AND
[Do we want to see an increase in this Measure?]=1,
[TmsrAmt]![Target] ,
[baseline]-([baseline]*0.0 5))
__________________
Below is the syntax you provided whch implies an "and" before the 2nd "IIF(...", when I need the syntax that will make it an "or" clause:
IIF([PM View prd].[DataTarget History].[Target] < [PM View prd].[Data].[Baseline] AND
[PM View prd].[Data].[Do we want to see an increase in this Measure?] = 0,
[PM View prd].[DataTarget History].[Target],
{on the other hand}
{OR}
IIF([PM View prd].[DataTarget History].[Target] > [PM View prd].[Data].[Baseline] AND
[PM View prd].[Data].[Do we want to see an increase in this Measure?] = 1,
[PM View prd].[DataTarget History].[Target],
[PM View prd].[Data].[Baseline] - ([PM View prd].[Data].[Baseline] * .05)))
Both IIF statements produce the same result. In My COGNOS statement noted above, you will notice an "OR" which somehow needs to be included.
Your sugestion reproduced in ACCESS systax below does the same as my above noted ACCESS syntax:
TG: IIf([TmsrAmt]![Target]<[ba
[Do we want to see an increase in this Measure?]=0,
[TmsrAmt]![Target] ,
-------here i need:
{on the other hand}
{OR}
IIf([TmsrAmt]![Target]>[ba
[Do we want to see an increase in this Measure?]=1,
[TmsrAmt]![Target] ,
[baseline]-([baseline]*0.0
__________________
Below is the syntax you provided whch implies an "and" before the 2nd "IIF(...", when I need the syntax that will make it an "or" clause:
IIF([PM View prd].[DataTarget History].[Target] < [PM View prd].[Data].[Baseline] AND
[PM View prd].[Data].[Do we want to see an increase in this Measure?] = 0,
[PM View prd].[DataTarget History].[Target],
{on the other hand}
{OR}
IIF([PM View prd].[DataTarget History].[Target] > [PM View prd].[Data].[Baseline] AND
[PM View prd].[Data].[Do we want to see an increase in this Measure?] = 1,
[PM View prd].[DataTarget History].[Target],
[PM View prd].[Data].[Baseline] - ([PM View prd].[Data].[Baseline] * .05)))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Your logic was right.
I finnally got the ACCESS syntax needed to work by applying an appropriate (-) sign and an extra parenthesis:
TG: IIf([TmsrAmt]![Target]<[ba seline] And [Do we want to see an increase in this Measure?]=0,[TmsrAmt]![Tar get],IIf([ TmsrAmt]![ Target]>[b aseline] And [Do we want to see an increase in this Measure?]=-1,[TmsrAmt]![Ta rget],[bas eline]-([b aseline]*0 .05)))
I finnally got the ACCESS syntax needed to work by applying an appropriate (-) sign and an extra parenthesis:
TG: IIf([TmsrAmt]![Target]<[ba
Regardless, the best way to get that much logic in access is to create a function.
I'm guessing at your datatypes, and set them to Decimal.
Open in new window
Once you've defined this in a module in your access file. reference it in your query by calling it like this:
TG: MyCognosLogic([TmsrAmt]![T