473,327 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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

Mar 18 '07 #1
9 3034

"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
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
"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
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
"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
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
"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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Stephan | last post by:
Hi, I'm using Visual Studio 2003 (C#) with the integrated Crystal Report software and have the following question: How can I assign a value (string) to an unbound (string) field in Crystal...
3
by: Dos Lil | last post by:
I have a unbound field in the subform(for calculating the datediff) which has the control property ==DateDiff("n",,). I have another unbound field in the main form which is for displaying the...
2
by: alex | last post by:
I need a more advanced formula than just an average for calculating items rating. I have: raitng value is on scale 1 to 10. s - sum of all ratings for an item n - number of rates (votes)
18
by: TORQUE | last post by:
Hi, Im wondering if anyone can help me with a problem. I have a form with more than 50 unbound fields. Some of the fields will be blank from time to time. This seems to be where im having...
1
by: anil | last post by:
hello all i am anil kumar, pursuing master's degree in Computer applications . now i have got some opensource project. that is related to USB sniffing. tha t is snoopypro. Actalluy this is...
0
by: bylabylamo | last post by:
hi , can anyone of u please give me an idea of how to convert xml to json using java.it is very urgently required.
0
by: HR Head | last post by:
We urgently require following skill set for singapore client SAP CO-COPA Functional Lead: Person with a strong Costing background. Should have hands on SAP "COPA" experience Minimum two...
1
by: HR Head | last post by:
Dear All, Fresh Programmers Required Urgently Visual Foxpro / VB / .NET Skill set required :- Visual Foxpro 1 or 2 years Experince Location:- Malaysia / Singapore Qulification ;- Any...
6
kcdoell
by: kcdoell | last post by:
Hello: I three fields on a continuous form: , & On the same form I have an unbound text box with the following formula in the control source: =Sum(IIf(=50,,0)) This worked great.
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.