Using VB to calculate a specific date

[barryg80]

Hi there,



I am trying to figure out how to calculate a Tax Deadline date for a new Access DB. Each year a client will have a record relating to their return with various deadlines in it. One deadline is 9 months after the companies year end but not later than the 21st day of that month. So for example with a year end of 31/12/13, their filing deadline is 21/09/13. I am putting this code behind the On_Change function of the CurrentYearEnd field.

So I have figured out how to use variables to add 9 months onto the Year End as follows:

Private Sub CurrentYearEnd_Change()


Dim dtCurrentYearEnd As Date
Dim dtCTFilingDeadlineDate As Date


dtCurrentYearEnd = CurrentYearEnd.text


dtCTFilingDeadlineDate = DateAdd("m", 9, dtCurrentYearEnd)
CTFilingDeadlineDate.SetFocus
CTFilingDeadlineDate.text = dtCTFilingDeadlineDate


This works, however how do I strip this date back to the 21st of the month?

Many thanks for any help

[JoeM]

Here is the logic you will want to employ.
You can use the "Day" function to calculate the day of your calculated date. If it is greater than 21, then change the day of your calculated date to 21, else just return the calculated date.
See if you can get that to work out. If you run into trouble and need help writing it, post back.

One other question. Why are you doing this in VBA code, instead of just creating a calculated field in a query (which is always dynamic)?

[barryg80]

Thanks JoeM will look in to the day function suggestion now. Doing this in VBA code because I want to store the records year on year for each client for referral purposes. So a return for 2013, then next year for 2014 etc. Is my logic flawed?

[JoeM]

Is the year end field already stored for each year? If so, then the query calculation method should work.

[rpeare]

Private Sub CurrentYearEnd_Change()
Dim dtCurrentYearEnd As Date
Dim dtCTFilingDeadlineDate As Date

dtCurrentYearEnd = CurrentYearEnd.Text
dtCTFilingDeadlineDate = DateAdd("m", 9, dtCurrentYearEnd)

If CInt(DatePart("d", dtCTFilingDeadlineDate)) > 21 Then
    dtCTFilingDeadlineDate = DateAdd("d", 21 - CInt(DatePart("d", dtCTFilingDeadlineDate)), dtCTFilingDeadlineDate)
End If

ctfilingdeadlinedate.SetFocus
ctfilingdeadlinedate.Text = dtCTFilingDeadlineDate
end sub

[rpeare]

Just a separate note here, JoeM is correct in saying that you really do not need to store this value. It can be calculated any time you access the record either through a query or as a value on a form.

[barryg80]

Ok thanks for all the feedback. Yes the Year End is stored for each record. Its gonna be the same most years except incremented by 1 year but may occasionally change.

[barryg80]

One thing about calculating the value, if I wish to report on it and prior years can I do that with it calculated? Thanks

[JoeM]

One thing about calculating the value, if I wish to report on it and prior years can I do that with it calculated? Thanks

It all depends on your data. If the prior year end date value is out there to do the calculation on, then you should be able to do that.
As long as you have the values to do your calculation, you can do the calculation.
A general rule of thumb with Access is to never store any value which can be calculated from other values that already exist.