Increase the date in a textbox by 364 days

[philfloyduk]

Hi.

I have a textbox on a form that contains a delivery date, and another textbox for the user to enter an expiry date. I would like the form to automatically increase the delivery date by a year, less one day when i populate the form.

Thank you in advance for any help provided

Phil

 

[JamesW]

EDIT: Rechecking code

 

[lynxbci]

This will change your date (one caviat) is that if it is 1/1/yyyy it goes to 31/12/yyyy but hopefully you can work round that.

Private Sub TextBox2_AfterUpdate()

Me.TextBox1.Value = DateAdd("yyyy", 1, Me.TextBox1.Value)
Me.TextBox1.Value = DateAdd("d", -1, Me.TextBox1.Value)

End Sub

 

[philfloyduk]

Hi James and thanks for your response.

Your code does the job very nicely until you have a delivery date of the 1st, at which point it gives an error.

I've just noticed lynxbci's response which appears to address that issue so I'll try it now. There won't be a problem with the 1st of January, we'll be closed!

Thanks very much

 

[JamesW]

Yeah sorry Phil, noticed that as soon as I posted it.

 

[philfloyduk]

I've just tried your method lynxbci and we have a different problem... if the date is the 1st of the month then it gives you a date of the 1st of the previous month, in the next year.

For example:

A delivery date of 01/06/2011 gives an expiry date of 01/05/2012

Dates are such a pain with Excel and VBA!!! Any ideas?

 

[lynxbci]

Hi

Are you sure i have just tried 01/06/2011 and got 31/05/2012

Kev

 

[JamesW]

I've just tried your method lynxbci and we have a different problem... if the date is the 1st of the month then it gives you a date of the 1st of the previous month, in the next year.

For example:

A delivery date of 01/06/2011 gives an expiry date of 01/05/2012

Dates are such a pain with Excel and VBA!!! Any ideas?

Are you definatly using UK dates and not US dates? It looks like it thinks that 01/06 is 6th January.

 

[lynxbci]

Hi, it may need tweaking slightly for your input method

This code allows you to enter a date in textbox2 and updates textbox1.
It adds a year to the date then subs a day from that new date

Private Sub TextBox2_AfterUpdate()

Me.TextBox1.Value = DateAdd("yyyy", 1, Me.TextBox2.Value)
Me.TextBox1.Value = DateAdd("d", -1, Me.TextBox1.Value)

End Sub

 

[philfloyduk]

I've just modified the code to the following:

Me.tbExpiry.Value = Format(DateAdd("yyyy", 1, Me.tbDeliveryDate.Value), "dd/mm/yyyy")
Me.tbExpiry.Value = Format(DateAdd("d", -1, Me.tbDeliveryDate.Value), "dd/mm/yyyy")

and it works (tbDeliveryDate is already formatted _afterupdate).

Thanks guys, it's so much more efficient than us typing it like we have done in the past!

Phil