Manipulating Dates in Access

[Donna]

I want to be able to enter a date into a text box (the format has to be 200302), then in another text box I want it to automatically enter the date 12 months previous i.e. 200203.

I have tried doing this by entering a formula into the control source but with little success.

Is this possible and if so how?

Thanks

 

[Dugantrain]

With a little VBA help, this should be very simple. The way you have your date syntax, it looks like a numerical field rather than a date/time field. If this is the case then:

Private Sub OneYearBefore_afterUpdate()
me.yoursecondtextboxname=me.youfirsttextboxname-100
End Sub

I think that's right, it looks like you may have your syntax for your dates in your example wrong; I assume 200203 is March, 2002 and 200302 is February 2003? If your syntax is correct, then I guess the formula would be

me.yoursecondtextboxname=me.youfirsttextboxname-99

 

[Donna]

Yes it was because I was using a numeric field although I did not need to use VBA. I simply typed this formula: =IIf(Right([FirstBox],2)=12,[FirstBox]+89,[FirstBox]-99) into controlsource in my second text box and this seems to have done the job.

Thanks very much for you help

:D

 

[Dugantrain]

Wow, I thought I was the guru; I didn't even know that you could put a function into the Control Source! I thought it was just for binding controls to fields within tables. Learn something new every day, right?