Trouble with a simple formula

[deaconj999]

Hi All,

I have been trying to get this to work in a form to add 1 3 or 5 years
for the choice in the drop down [risk factor] to an entered date field
[date of last FSMP] and display it automatically.

=IIf([Risk Factor]="High - Annual",DateAdd("yyyy",1,[Date of Last
FSMP]), IIf([Risk Factor]="Med - 3 Yearly",DateAdd("yyyy",3,[Date of
Last
FSMP]), IIf([Risk Factor]="Low - 5 Yearly",DateAdd("yyyy",5,[Date of
Last FSMP]))))))

I am self taught and am using

=IIf([Risk factor]="HIGH - annual",[Date of Last FSMP]+365,IIf([Risk
factor]="MED - 3 yearly",[Date of Last FSMP]+1095,IIf([Risk
factor]="LOW - 5 yearly",[Date of Last FSMP]+1825,IIf([Risk factor]="N/
A",Date()))))

but would like to tuse the one above but I keep getting invalid syntax
error

PLEASE HELP

 

[strive4peace]

IIF
---

on long equations, it helps to reformat them...

=IIF
(
[Risk factor]="HIGH - annual"
,[Date of Last FSMP]+365
, IIF
(
[Risk factor]="MED - 3 yearly"
,[Date of Last FSMP]+1095
, IIF
(
[Risk factor]="LOW - 5 yearly"
, [Date of Last FSMP]+1825
, IIF
(
[Risk factor]="N/A"
, Date()
, [color_red]value-if-false[/QUOTE]
)
)
)
)

syntax of IIF:

IIF(condition, value-if-true, value-if false)

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[deaconj999]

IIF
---

on long equations, it helps to reformat them...

=IIF
(
[Risk factor]="HIGH - annual"
,[Date of Last FSMP]+365
, IIF
(
[Risk factor]="MED - 3 yearly"
,[Date of Last FSMP]+1095
, IIF
(
[Risk factor]="LOW - 5 yearly"
, [Date of Last FSMP]+1825
, IIF
(
[Risk factor]="N/A"
, Date()
, [color_red]value-if-false

)
)
)
)

syntax of IIF:

IIF(condition, value-if-true, value-if false)

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi All,

I have been trying to get this to work in a form to add 1 3 or 5 years
for the choice in the drop down [risk factor] to an entered date field
[date of last FSMP] and display it automatically.

=IIf([Risk Factor]="High - Annual",DateAdd("yyyy",1,[Date of Last
FSMP]), IIf([Risk Factor]="Med - 3 Yearly",DateAdd("yyyy",3,[Date of
Last
FSMP]), IIf([Risk Factor]="Low - 5 Yearly",DateAdd("yyyy",5,[Date of
Last FSMP]))))))

I am self taught and am using

=IIf([Risk factor]="HIGH - annual",[Date of Last FSMP]+365,IIf([Risk
factor]="MED - 3 yearly",[Date of Last FSMP]+1095,IIf([Risk
factor]="LOW - 5 yearly",[Date of Last FSMP]+1825,IIf([Risk factor]="N/
A",Date()))))

but would like to tuse the one above but I keep getting invalid syntax
error

PLEASE HELP- Hide quoted text -

- Show quoted text -[/QUOTE]

Crystal thanks for that, I made a mistake what I wanted is

=IIf([Risk Factor]="High - Annual",DateAdd("yyyy",1,[Date of Last
FSMP]), IIf([Risk Factor]="Med - 3 Yearly",DateAdd("yyyy",3,[Date of
Last
FSMP]), IIf([Risk Factor]="Low - 5 Yearly",DateAdd("yyyy",5,[Date of
Last FSMP]))))))

corrected please any chance ?

Joe

 

[John W. Vinson]

Hi All,

I have been trying to get this to work in a form to add 1 3 or 5 years
for the choice in the drop down [risk factor] to an entered date field
[date of last FSMP] and display it automatically.

=IIf([Risk Factor]="High - Annual",DateAdd("yyyy",1,[Date of Last
FSMP]), IIf([Risk Factor]="Med - 3 Yearly",DateAdd("yyyy",3,[Date of
Last
FSMP]), IIf([Risk Factor]="Low - 5 Yearly",DateAdd("yyyy",5,[Date of
Last FSMP]))))))

I am self taught and am using

=IIf([Risk factor]="HIGH - annual",[Date of Last FSMP]+365,IIf([Risk
factor]="MED - 3 yearly",[Date of Last FSMP]+1095,IIf([Risk
factor]="LOW - 5 yearly",[Date of Last FSMP]+1825,IIf([Risk factor]="N/
A",Date()))))

but would like to tuse the one above but I keep getting invalid syntax
error

PLEASE HELP

For multiple nested IIF's, it's often better to use the Switch() function
instead. It lets you select any reasonable number of pairs of values; it will
evaluate them left to right, and when it first encounters a TRUE value in the
first member of a pair, it will return the second member of the pair and quit.

I'd also suggest taking the DateAdd out of the function, and having the
function just return a number.

Try:

DateAdd("yyyy",
Switch(
[Risk Factor] = "High - Annual", 1,
[Risk Factor]="Med - 3 Yearly", 3,
[Risk Factor]="Low - 5 Yearly", 5,
[Risk factor]="N/A", 0),
[Date Of Last FSMP])

This doesn't cover the case where the Risk Factor value is something OTHER
than these specific values. What date do you want if the [Risk Factor] value
is "See agent - special case" or "Other" or null?

John W. Vinson [MVP]

 

[deaconj999]

Hi All,

I have been trying to get this to work in a form to add 1 3 or 5 years
for the choice in the drop down [risk factor] to an entered date field
[date of last FSMP] and display it automatically.

=IIf([Risk Factor]="High - Annual",DateAdd("yyyy",1,[Date of Last
FSMP]), IIf([Risk Factor]="Med - 3 Yearly",DateAdd("yyyy",3,[Date of
Last
FSMP]), IIf([Risk Factor]="Low - 5 Yearly",DateAdd("yyyy",5,[Date of
Last FSMP]))))))

I am self taught and am using

=IIf([Risk factor]="HIGH - annual",[Date of Last FSMP]+365,IIf([Risk
factor]="MED - 3 yearly",[Date of Last FSMP]+1095,IIf([Risk
factor]="LOW - 5 yearly",[Date of Last FSMP]+1825,IIf([Risk factor]="N/
A",Date()))))

but would like to tuse the one above but I keep getting invalid syntax
error

PLEASE HELP

For multiple nested IIF's, it's often better to use the Switch() function
instead. It lets you select any reasonable number of pairs of values; it will
evaluate them left to right, and when it first encounters a TRUE value in the
first member of a pair, it will return the second member of the pair and quit.

I'd also suggest taking the DateAdd out of the function, and having the
function just return a number.

Try:

DateAdd("yyyy",
Switch(
[Risk Factor] = "High - Annual", 1,
[Risk Factor]="Med - 3 Yearly", 3,
[Risk Factor]="Low - 5 Yearly", 5,
[Risk factor]="N/A", 0),
[Date Of Last FSMP])

This doesn't cover the case where the Risk Factor value is something OTHER
than these specific values. What date do you want if the [Risk Factor] value
is "See agent - special case" or "Other" or null?

John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

John,

This works well, thanks for the formula, have a very good day.

Joe