Unbound Formula required urgently please

Hi All,

I am using this unbound to add the frequency to a date entered

=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 rather it take into account the leap year, I am using the
medium date format in my database, I would rather not mess about
creating another table so please advise on the way I can change this
formulae to add 1 3 or 5 years to a medium date dd-mmm-yy.


Anyone else got any ideas please I am desperately short of time

Thanks in advance

re: Unbound Formula required urgently please

"deaconj999" <deaconj999@btinternet.comwrote in message
news:1174241451.009273.123370@e65g2000hsc.googlegr oups.com...Use DateAdd() or DateSerial() instead of just adding numbers. Both of those
would account for leap years.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

re: Unbound Formula required urgently please

On 18 Mar, 18:18, "Rick Brandt" <rickbran...@hotmail.comwrote:Thanks for the reply, but as I am self taught I am not having much
luck adding the DateAdd() function into my formula I am getting errors
invalid syntax you may have entered an operand without an operator
what does this mean ???????

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

re: Unbound Formula required urgently please

"deaconj999" <deaconj999@btinternet.comwrote in message
news:1174243011.791344.123620@n59g2000hsh.googlegr oups.com...To add years with DateAdd() use "yyyy", not "yy". Your formatting makes no
difference to how dates are stored or manipulated.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

re: Unbound Formula required urgently please

On 18 Mar, 18:42, "Rick Brandt" <rickbran...@hotmail.comwrote:Rick,

=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 still get the invalid syntax error "you may have entered an operand
without an operator"

This must be a typo in the formulae perhaps obviously I am trying to
add the value 1 3 or 5 years from the [ to the date entered in [Date
of Last FSMP]

re: Unbound Formula required urgently please

"deaconj999" <deaconj999@btinternet.comwrote in message
news:1174246313.437516.147550@l77g2000hsb.googlegr oups.com...Your first two DateAdd() functions don't have a closing parenthesis.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

re: Unbound Formula required urgently please

On Mar 18, 7:35 pm, "Rick Brandt" <rickbran...@hotmail.comwrote:Maybe, but I have no idea where they go to be honest

re: Unbound Formula required urgently please

"deaconj999" <deaconj999@btinternet.comwrote in message
news:1174247216.001602.268860@y80g2000hsf.googlegr oups.com...The syntax for DateAdd() is..

DateAdd("interval", number, Date to add to)

The closing parenthesis therefore go immediately after the date you are adding
to.

=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]))))


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

re: Unbound Formula required urgently please

On Mar 18, 8:03 pm, "Rick Brandt" <rickbran...@hotmail.comwrote:Rick,

Many thanks, it shows #Name? in the Date of next FSMP box where the
formulae is, any final ideas ?

re: Unbound Formula required urgently please

"deaconj999" <deaconj999@btinternet.comwrote in message
news:1174252696.983883.152920@n59g2000hsh.googlegr oups.com...My usual advice with long-winded formulas like this is to break them down into
simpler components for de-bugging and them re-combine the debugged components.

In three new fields of your query enter...

OneYearTest: DateAdd("yyyy", 1, [Date of Last FSMP])
ThreeYearTest: DateAdd("yyyy", 3, [Date of Last FSMP])
FiveYearTest: DateAdd("yyyy", 5, [Date of Last FSMP])

....and make sure that each of those return the values you want. In another new
query field put...

IIfTest: IIf([Risk Factor]="High - Annual", 1, IIf([Risk Factor]="Med - 3
Yearly", 3, IIf([Risk Factor]="Low - 5 Yearly", 5)))

....and see if that produces output of 1, 3, and 5 as appropriate.

When all of those work then you should be able to just replace each number in
the last expression with the appropriate working YearTest expression.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com