By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,369 Members | 950 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,369 IT Pros & Developers. It's quick & easy.

Unbound Formula required urgently please

P: n/a
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

Mar 18 '07 #1
Share this Question
Share on Google+
9 Replies


P: n/a

"deaconj999" <de********@btinternet.comwrote in message
news:11**********************@e65g2000hsc.googlegr oups.com...
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
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
Mar 18 '07 #2

P: n/a
On 18 Mar, 18:18, "Rick Brandt" <rickbran...@hotmail.comwrote:
"deaconj999" <deaconj...@btinternet.comwrote in message

news:11**********************@e65g2000hsc.googlegr oups.com...


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

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- Hide quoted text -

- Show quoted text -
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]))))

Mar 18 '07 #3

P: n/a
"deaconj999" <de********@btinternet.comwrote in message
news:11**********************@n59g2000hsh.googlegr oups.com...
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]))))
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
Mar 18 '07 #4

P: n/a
On 18 Mar, 18:42, "Rick Brandt" <rickbran...@hotmail.comwrote:
"deaconj999" <deaconj...@btinternet.comwrote in message

news:11**********************@n59g2000hsh.googlegr oups.com...
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]))))

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

Mar 18 '07 #5

P: n/a
"deaconj999" <de********@btinternet.comwrote in message
news:11**********************@l77g2000hsb.googlegr oups.com...
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]
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
Mar 18 '07 #6

P: n/a
On Mar 18, 7:35 pm, "Rick Brandt" <rickbran...@hotmail.comwrote:
"deaconj999" <deaconj...@btinternet.comwrote in message

news:11**********************@l77g2000hsb.googlegr oups.com...
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]

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
Maybe, but I have no idea where they go to be honest

Mar 18 '07 #7

P: n/a
"deaconj999" <de********@btinternet.comwrote in message
news:11**********************@y80g2000hsf.googlegr oups.com...
On Mar 18, 7:35 pm, "Rick Brandt" <rickbran...@hotmail.comwrote:
>"deaconj999" <deaconj...@btinternet.comwrote in message
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

Maybe, but I have no idea where they go to be honest
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
Mar 18 '07 #8

P: n/a
On Mar 18, 8:03 pm, "Rick Brandt" <rickbran...@hotmail.comwrote:
"deaconj999" <deaconj...@btinternet.comwrote in message

news:11**********************@y80g2000hsf.googlegr oups.com...
On Mar 18, 7:35 pm, "Rick Brandt" <rickbran...@hotmail.comwrote:
"deaconj999" <deaconj...@btinternet.comwrote in message
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
Maybe, but I have no idea where they go to be honest

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
Rick,

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

Mar 18 '07 #9

P: n/a
"deaconj999" <de********@btinternet.comwrote in message
news:11**********************@n59g2000hsh.googlegr oups.com...
Rick,

Many thanks, it shows #Name? in the Date of next FSMP box where the
formulae is, any final ideas ?
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
Mar 18 '07 #10

This discussion thread is closed

Replies have been disabled for this discussion.