Date calculation in textbox

[Bosworth]

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

[pbaldy]

I would try using DateAdd() to add a year and then subtract a day from that. DateAdd() should handle leap years correctly.

[Bosworth]

I would try using DateAdd() to add a year and then subtract a day from that. DateAdd() should handle leap years correctly.

Paul, thanks for the suggestion here. This looks like the "right" way to do it. I tried applying it but I suspect it doesn't plug nicely into a textbox, rather belongs in a VBA event, which I'm not too savvy with. I'll have to do some research. Thanks again, very appreciated.

[pbaldy]

It should work either way. What did you try?

[Bosworth]

Well, when you said "it should work either way", I took a closer look at it and found the right syntax....

=DateAdd("yyyy",1,[Firsttextbox]) ..... where it used to be...... DateAdd("y",1,[Firsttextbox])

Thank you Paul!

[pbaldy]

Happy to help and welcome to the site by the way!

[Bosworth]

Appreciated Paul. I hope I don't ask the same questions you've seen repeated ad nauseum. I know it can be exhausting but sometimes I need a little kick-start if I can't find the answer on my own. The last time I was really working with Access was back in Office XP, so the rust has really accumulated. I'll probably be the occasional visitor as I'm no longer using Access on a professional basis. Just a mere hobbyist now.

[pbaldy]

Happy to help! Don't worry, at this point it's hard to ask a question that hasn't been asked before in one form or another.