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

 

[Norie]

I see you've got an answer but I'll post this anyway.

Private Sub TextBox2_AfterUpdate()
Dim dt1 As Date
Dim dt2 As Date
 
    If IsDate(TextBox2.Value) Then

        dt1 = DateValue(TextBox2.Value)
        
        dt2 = DateSerial(Year(dt1) + 1, Month(dt1), Day(dt1)) - 1
        
        TextBox1.Value = Format(dt2, "dd mmm yy")
        
    End If
    
End Sub

I always forget DateAdd exists in Excel, more used to using it in Access.

PS Isn't 31/12/2011 a year less a day after 1/1/2011?

 

[lynxbci]

Hi,

Your code needs changing, or it won't change the year, as it will amend the year and then amend the day based on the originmal year. Tweak line 2 of your code and voila all is good.

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

------------------------------------------------------
Everyone is equal in the Double Dream House

 

[philfloyduk]

Ahhhhh yes! Thumbs up

Thanks.

Thanks to Norie too

 

[philfloyduk]

I'm going to push my luck now....

Some of our rentals have a set expiry date of 14th June. How would I get a 14/06/"next year" date?

 

[Norie]

Only add a year, ie dont' subtract a day.

In my code I just commented out -1.

 

[philfloyduk]

Got it!

Me.tbExpiry.Value = Format(DateAdd("yyyy", 1, Me.tbDeliveryDate.Value), "14/06/yyyy")

 

[lynxbci]

2 seconds ahead of me. Nice



_____________________________________________
Everyone is equal in th Double Dream House