If Expressions

[Guest]

I have 3 fields in a querry.
I have field number 1 in a querry (called Original_SVS_ID) that can contains:

hurricane
undeliverable
high value
one time
nenver

And I have field number 2 (called Reward_Period) that contains a year, and I
have number 3 field (called Reason)

I want to create an expresion in which if the word 'hurricane' is found in
field 1, and field number 2 contains for example the year '2007', I want to
create a field that will populate field 1 and 2 together with a hyphen (-).
In this case will be 'Hurricane-2007'. If the word undeliverable is found,
and 2006 in field 2, I would like 'Undeliverable-2007' . If the word 'High
Value' is found, and 2006 in field 2, I would like 'High Value-2007'

If none of the 3 words above are found, lets say field number 1 contains
other words, I would like to go to field 3 and then populate what field
number 3 has with the hypen and period.

See Example:

Field 1 Field 2 Field 3
Expression
Hurricane 2007 One time
Hurricane-2007
Undeliverable 2006 One time
Undeliverable-2006
Undeliverable 2007 Never
Undeliverable-2007
High Value 2006 Lost
High Value-2006
One time 2006 Lost
Lost-2006
Never 2007 One time One
time-2007

Now,

I have the following expresion but it is not working:

Expr1: IIf(CurrentWeek!Original_SVS_ID In ("UNDELIVERABLE"),"Undeliverable -
" & CurrentWeek!Reward_Period) OR
IIf(CurrentWeek!Original_SVS_ID In ("HURRICANE"),"Hurricane - " &
CurrentWeek!Reward_Period)
OR IIf(CurrentWeek!Original_SVS_ID In ("HIGH VALUE"),"High Value - " &
CurrentWeek!Reward_Period)

and then I would like to finish the statement that if none of the expresions
above are met, then look for the reason field and get the reason field plus
hyphen plus the field 2 which is reward_period

CurrentWeek!Reason & " - " & CurrentWeek!Reward_Period)

 

[Marshall Barton]

I have 3 fields in a querry.
I have field number 1 in a querry (called Original_SVS_ID) that can contains:

hurricane
undeliverable
high value
one time
nenver

And I have field number 2 (called Reward_Period) that contains a year, and I
have number 3 field (called Reason)

I want to create an expresion in which if the word 'hurricane' is found in
field 1, and field number 2 contains for example the year '2007', I want to
create a field that will populate field 1 and 2 together with a hyphen (-).
In this case will be 'Hurricane-2007'. If the word undeliverable is found,
and 2006 in field 2, I would like 'Undeliverable-2007' . If the word 'High
Value' is found, and 2006 in field 2, I would like 'High Value-2007'

If none of the 3 words above are found, lets say field number 1 contains
other words, I would like to go to field 3 and then populate what field
number 3 has with the hypen and period.

See Example:

Field 1 Field 2 Field 3
Expression
Hurricane 2007 One time
Hurricane-2007
Undeliverable 2006 One time
Undeliverable-2006
Undeliverable 2007 Never
Undeliverable-2007
High Value 2006 Lost
High Value-2006
One time 2006 Lost
Lost-2006
Never 2007 One time One
time-2007

Now,

I have the following expresion but it is not working:

Expr1: IIf(CurrentWeek!Original_SVS_ID In ("UNDELIVERABLE"),"Undeliverable -
" & CurrentWeek!Reward_Period) OR
IIf(CurrentWeek!Original_SVS_ID In ("HURRICANE"),"Hurricane - " &
CurrentWeek!Reward_Period)
OR IIf(CurrentWeek!Original_SVS_ID In ("HIGH VALUE"),"High Value - " &
CurrentWeek!Reward_Period)

and then I would like to finish the statement that if none of the expresions
above are met, then look for the reason field and get the reason field plus
hyphen plus the field 2 which is reward_period

CurrentWeek!Reason & " - " & CurrentWeek!Reward_Period)

Maybe this is will do all that:

IIf(Original_SVS_ID In ("Hurricane", "Undeliverable", "High
Value"), Original_SVS_ID, Reason) & "-" & Reward_Period

 

[Guest]

Thanks... It worked

Maybe this is will do all that:

IIf(Original_SVS_ID In ("Hurricane", "Undeliverable", "High
Value"), Original_SVS_ID, Reason) & "-" & Reward_Period