Access 2007, Windows 7
On a form I have 2 textboxes, 1 for user input, the other a calculated date. The two values together represent a "Leave Year". The second textbox contains, [Firsttextbox]&" - "&([Firsttextbox]+364). Simple enough so far. The expected result from entering, for example, 2/10/2015, is 2/10/2015 - 2/9/2016, which is exactly what I need. But, using a hard figure like 364 will fail on 3/2/2015 when the extra day in Feb 2016 results in, 3/2/2015 - 2/29/2015. To compensate for this, I've added,
"=IIf([Firsttextbox]>"2/28/2015",1,0)" to a third textbox and added it to the calculation as follows;
[Firsttextbox]&" - "&([Firsttextbox]+364+[Thirdtextbox]) So far so good....almost.
The "Leave Year" calculated is what I want, until 10/1/2015, when something goes awry. At 10/1/2015, the expression in the third text box evaluates to 0,and I lose my extra day.
If the calculation is an Access 2007 problem/issue, will it evaluate correctly if it's migrated to a new version? And, if there's an easier way to get the result, please educate me.
Another thing I need to do is similar, in that a date is entered and calculated to 7 days prior. If the calculated date falls on a Saturday, I need to have it adjust to Friday, the day before. Also, if another calculated date falls on Saturday or Sunday, I need to have it adjust to the following Monday. These are two separate calculated fields, but both are necessary. Any suggestions?
Any help is appreciated.
Bos