Changing a Calculate field (Date) depending on Current date
Hi,
I have three suggestions.
1) If the enrolldate is date/time datatype then try using
the dateadd function. You can add 6 mos and let VB worry
about leap years etc. When your function adds 6 mos to
december does it return 18th month?
2) If you make enrolldate a date datatype you don't need
to store the nadate at all. It's a good idea not to save
calulated data. Unless there were exceptions to the 6 mo
thing i would use 'DateAdd(enrolldate,"m",6) AS NADate'
(lookup dateadd in access help because this syntax isn't
exact) in queries, reports, and business logic code. That
way you don't have to worry about updates.
3)If you want your code to loop until the update variable
is greater than today just use todays date instead of
update. ie:
If (Update <= CurDate) Then
NADate = DateSerial(Year(Date()), Month(Date()) + 6,
Day(Date()))
Else
NADate = Update
End If
Hope this helps,
Josh
Quote:
>-----Original Message-----
>I have a field called Enroll Date that I am using to
generate the
>calculated field called NA (needs assessment) Date. The
NA Date field is a
>continuous cycle, meaning that every 6 months after the
Enroll date, a needs
Quote:
>assessment has to be performed. I am using the following
function:
>----------------------------------------------------------
------------------
>--
>Function NADateString(EnrollDate As Variant) As String
>Dim CurDate As Date
>Dim Update As Date
>CurDate = Date
>If IsNull(EnrollDate) Then
> NADate = "No Enroll Date"
>Else
> 'Generates date 6 months after EnrollDate correctly
> Update = DateSerial(Year(EnrollDate), Month
(EnrollDate) + 6,
>Day(EnrollDate))
> 'Attempted this code to make it check current date
and continuously
>update every
> '6 months. Only works for 2 cycles
> If (Update <= CurDate) Then
> NADate = DateSerial(Year(Update), Month(Update)
+ 6, Day(Update))
> Else
> NADate = Update
> End If
>End If
>End Function
>----------------------------------------------------------
------------------
>---------
>I believe that I need a loop to make it work correctly,
but having problems
>with the coding. I would really appreciate any comments
or suggestions.
>.
