Formatting and working with dates/months

My database has a calculated date when calibrations are due again, for
example, =date()+90 from the last calibration date. My problem is, the
calibration can be done anytime during the month - so if it is done on the
1st or 2nd, then it shows up on the overdue report in the month that it is
actually due. For example: 9-1-08 is overdue 12-2-08. But, since we don't
have to do the calibration until the 31st of Dec, this particular calibration
stays on the overdue list all month. How do I fix it so the overdue report
doesn't show this calibration as overdue until 1-1-09?

Since this is a "business rule", I would create a small user-defined function
to calculate the value. A function can be created in a module of business
rules and then easily changed/updated when the rules change.

I'm not sure I understand your example since 90 days after 9/1/2008 is
11/30/2008 so I would expect the next date to be in the month of November.
Dec 31 would be 121 days out from 9/1/2008.

Here is at least a starting point that you can modify as needed. This
function can be used in code, control sources, queries, etc like other
functions.

Public Function GetNextCalibration(datPrev As Date) As Date
Dim intDaysToAdd As Integer
Dim datTemp As Date
intDaysToAdd = 90
datTemp = DateAdd("d", intDaysToAdd, datPrev)
GetNextCalibration = DateSerial(Year(datTemp), Month(datTemp) = 1, 0)
End Function


--
Duane Hookom
Microsoft Access MVP


"Bob Waggoner" wrote:

My database has a calculated date when calibrations are due again, for
example, =date()+90 from the last calibration date. My problem is, the
calibration can be done anytime during the month - so if it is done on the
1st or 2nd, then it shows up on the overdue report in the month that it is
actually due. For example: 9-1-08 is overdue 12-2-08. But, since we don't
have to do the calibration until the 31st of Dec, this particular calibration
stays on the overdue list all month. How do I fix it so the overdue report
doesn't show this calibration as overdue until 1-1-09?

Diane,
Thank you. I should have checked my example more carefully - 90 days. I
wonder if I could trouble you to help me get this function to work? I am
brand new to functions. When I create this function in the modules tab of my
database (access 97 right now) - how do I call it?

I know how to use private and public subs - but functions...ah, that's a
brand new ball game that I am eagerly learning.

"Duane Hookom" wrote:

Since this is a "business rule", I would create a small user-defined function
to calculate the value. A function can be created in a module of business
rules and then easily changed/updated when the rules change.

I'm not sure I understand your example since 90 days after 9/1/2008 is
11/30/2008 so I would expect the next date to be in the month of November.
Dec 31 would be 121 days out from 9/1/2008.

Here is at least a starting point that you can modify as needed. This
function can be used in code, control sources, queries, etc like other
functions.

Public Function GetNextCalibration(datPrev As Date) As Date
Dim intDaysToAdd As Integer
Dim datTemp As Date
intDaysToAdd = 90
datTemp = DateAdd("d", intDaysToAdd, datPrev)
GetNextCalibration = DateSerial(Year(datTemp), Month(datTemp) = 1, 0)
End Function


--
Duane Hookom
Microsoft Access MVP


"Bob Waggoner" wrote:

My database has a calculated date when calibrations are due again, for
example, =date()+90 from the last calibration date. My problem is, the
calibration can be done anytime during the month - so if it is done on the
1st or 2nd, then it shows up on the overdue report in the month that it is
actually due. For example: 9-1-08 is overdue 12-2-08. But, since we don't
have to do the calibration until the 31st of Dec, this particular calibration
stays on the overdue list all month. How do I fix it so the overdue report
doesn't show this calibration as overdue until 1-1-09?

Bob,
It's Duane, not Diane ;-)
You can create a function by copying the code I suggested into a new
standard module (or existing one) and then saving it. If it is a new module,
you might want to name the module "modBusinessCalcs".

You can use the function almost the exact way you would use built-in
functions. In a query, you could use:
SELECT [YourDateField] ,GetNextCalibration([YourDateField]) As NextDate
FROM tblYourTable;

You could also have a text box on a form or report with a control source of:
=GetNextCalibration([YourDateField])

--
Duane Hookom
Microsoft Access MVP


"Bob Waggoner" wrote:

Diane,
Thank you. I should have checked my example more carefully - 90 days. I
wonder if I could trouble you to help me get this function to work? I am
brand new to functions. When I create this function in the modules tab of my
database (access 97 right now) - how do I call it?

I know how to use private and public subs - but functions...ah, that's a
brand new ball game that I am eagerly learning.

"Duane Hookom" wrote:

Since this is a "business rule", I would create a small user-defined function
to calculate the value. A function can be created in a module of business
rules and then easily changed/updated when the rules change.

I'm not sure I understand your example since 90 days after 9/1/2008 is
11/30/2008 so I would expect the next date to be in the month of November.
Dec 31 would be 121 days out from 9/1/2008.

Here is at least a starting point that you can modify as needed. This
function can be used in code, control sources, queries, etc like other
functions.

Public Function GetNextCalibration(datPrev As Date) As Date
Dim intDaysToAdd As Integer
Dim datTemp As Date
intDaysToAdd = 90
datTemp = DateAdd("d", intDaysToAdd, datPrev)
GetNextCalibration = DateSerial(Year(datTemp), Month(datTemp) = 1, 0)
End Function


--
Duane Hookom
Microsoft Access MVP


"Bob Waggoner" wrote:

My database has a calculated date when calibrations are due again, for
example, =date()+90 from the last calibration date. My problem is, the
calibration can be done anytime during the month - so if it is done on the
1st or 2nd, then it shows up on the overdue report in the month that it is
actually due. For example: 9-1-08 is overdue 12-2-08. But, since we don't
have to do the calibration until the 31st of Dec, this particular calibration
stays on the overdue list all month. How do I fix it so the overdue report
doesn't show this calibration as overdue until 1-1-09?