Expressions

[Haile's]

I have a table with a list of hospital porters who are re-assessed every 3
years. How can I add an expression to this date so that it will change
automatically for me?

 

[BruceM]

Under what circumstances would you have the date change automatically?
Check Help for information about the DateAdd function, which may be what you
need, but I can't tell from here.

 

[John W. Vinson]

I have a table with a list of hospital porters who are re-assessed every 3
years. How can I add an expression to this date so that it will change
automatically for me?

What is "this date"? There's nothing in your first sentence which specifies
that. Please post the structure of your table, perhaps with an example record.

At a WILD GUESS, you have an AssessmentDate field in the table. you could
create a query with a calculated field

AssessmentDue: DateAdd("yyyy", 3, [AssessmentDate])

to calculate when the next assessment is due. Of course this may or may not be
the actual new date, since it might fall on a weekend or holiday, or the
assessor or porter might otherwise not be available on that date, so I would
not recommend actually updating the table.

 

[James A. Fortune]

I have a table with a list of hospital porters who are re-assessed every 3
years. How can I add an expression to this date so that it will change
automatically for me?

I have seen a similar need before. Part of ISO requirements is that
things like supplier performance and employee training need to be
reviewed periodically in management meetings and assigned a ReviewDate.
Note that a training ReviewDate is slightly different than a training
ExpirationDate. Instead of updating the ReviewDate's one by one,
management prefers to wait until the entire process is finished before
letting code that runs daily update all the ReviewDate's in one batch at
the beginning of each new period. If a ReviewDate is new, such as for a
new supplier or a new employee, it is left "as is," until the beginning
of the next period. The "Start" date functions calculate the beginning
of the period in which the date argument (usually today's date) falls.
The functions are set to strings since the values are to be sent to an
Excel spread sheet. Warning: It is incumbent upon management to
schedule and attend all the meetings required to perform their
assessments because the dates will increment whether the assessment is
actually made during the period or not. You should be able to adjust
the code for a three year period.

Const QUARTERLY = 3
Const BIANNUALLY = 7

....
strPut = MonthValueSixMonthStart(Date)
If Not IsNull(MyRS("ReviewDate")) Then
If DateDiff("m", MyRS("ReviewDate"), Date) <= BIANNUALLY Then
strPut = CStr(Format(MyRS("ReviewDate"), "m/yy"))
End If
End If
....

Public Function MonthValueSixMonthStart(theDate As Date) As String
If Month(theDate) <= 6 Then
MonthValueSixMonthStart = "1/" & Right(CStr(Year(theDate)), 2)
Else
MonthValueSixMonthStart = "7/" & Right(CStr(Year(theDate)), 2)
End If
End Function

Public Function MonthValueQuarterStart(theDate As Date) As String
MonthValueQuarterStart = CStr((DatePart("q", theDate) - 1) * 3 + 1) &
"/" & Right(CStr(Year(theDate)), 2)
End Function

James A. Fortune
(e-mail address removed)