Auto populate textbox with date according to date in other textbox

[Tcurtis]

I have a form with a button that puts the beginning and ending date in two textboxes. The start date is named Start_Date_Corp and the stop date is named Stop_Date_Corp. I would like to have another textbox that looks at the Start date and populates with a date that is 24 months prior to that date. Below is the VBA I have but I cannot make it work correctly. When I push the 2013 the first two boxes are correct - Start_Date_Corp 1/1/2013 -- Stop_Date_Corp 12/21/2013. The start date textbox I have for 24 months prior displays 1/1/1898. How can I make this box come up with the correct date? Or maybe just have that textbox look at the start date and then update using a function on the form?


Below is my VBA

Function Set_Metrics_24_month_Corp()

Dim Start_Date_Corp As Date
Dim Stop_Date_Corp As Date

Corp_Metric_2Yr_Stop = DateSerial(Year(Stop_Date_Corp), Month(Stop_Date_Corp) + 1, 0) + TimeSerial(23, 59, 59)
Corp_Metric_2Yr_Start = DateSerial(Year(Start_Date_Corp), Month(Start_Date_Corp) - 23, 1)

Forms![frm_Main]![NavigationSubform].Form![Corp_Metric_2Yr_Start].Value = Corp_Metric_2Yr_Start
Forms![frm_Main]![NavigationSubform].Form![Corp_Metric_2Yr_Stop].Value = Corp_Metric_2Yr_Stop

End Function

 

[kbrummert]

Do you have both fields formatted as mm/dd/yyyy? I played with this and it looks like it may be reading the source box wrong due to different formatting. Otherwise, it worked fine.

 

[Norie]

Use DateAdd to add the required months forward/backwards.

For example to get the date 24 months prior to the date in Start_Date_Corp use this.

DateAdd("m", -24, Start_Date_Corp.Value)

 

[Tcurtis]

I changed the format to mm/dd/yyyyy and still came up with the year dates of 1898. The formatting was Short Date and that does not work either. Not sure why this does not work.

 

[Tcurtis]

Not sure where to put the DateAdd at. I put it in the Textbox using Expression and it did not work.

 

[Norie]

I'm not sure where to put it either because I can't quite figure out what the posted code is meant to do.

When do you want the other textbox to be populated based on what's entered in Start_Date_Corp?

 

[Tcurtis]

After the Start date box has been updated. I tried refreshing the page but it was not updating. I have buttons for each year that populate the start and stop dates. After the dates are populated I would like the 24 month box to populates

 

[Norie]

You should look into using the AfterUpdate event of the Start_Date_Corp textbox, or add the code to the buttons that populate the start/stop dates.

 

[Micron]

One form is on a navigation form and the other is not? AFAIK, you can only load one form at a time from a navigation form, which ought to mean one of yours isn't open when this code runs. Not clear if more than one form is involved since you don't indicate what Corp_Metric_2Yr_Stop or Corp_Metric_2Yr_Start are. Variables? Form controls? If the former, are they declared somewhere and still available to this procedure? Or are you not using Option Explicit for all your modules? If controls, I'm surprised this would work as written without the form reference (Me. if code is on the same form as the control, or the complete Forms! reference if not, assuming the referenced form is open). You need to say where the code is located and provide more info about the number of forms involved and those variables (or whatever they are). Or step through the code and check that the values are present and correct. You might be subtracting/adding date values from some unexpected value. The only other thing I can think of is that your regional settings for Dates are coming into play.