Update field daily

[Dan Lavish]

I have a request date field and a reply due date field on my form with
the code listed below on the request date after update event i was
wondering if there is a way to update this daily because after five
days its over due the problem is the only way to change it from a date
to overdue is too reenter the date it also shows up on a report so any
help is cool thanks in advance

Dan

Private Sub Request_Date_AfterUpdate()
If Date >= GetBusinessDay(Me.Request_Date, 6) Then
Me.Action_Overdue = "OVERDUE"
Else
Me.Action_Overdue = GetBusinessDay(Me.Request_Date, 5)
End If
End Sub

' the getBusinessday is a module that skips business days and holidays
thanks to some one else!

 

[Tieske]

Why do you use a field for the due date? You can simply create a calculated
control that does this.
calculate it as:
=Iff(Date > GetBusinessDay([Request_Date], 5), "OVERDUE",
GetBusinessDay([Request_Date], 5))

you may have to change the afterupdate event to recalculate the control, if
this doesn't work automatically.

regards,
Tieske

PS. I never know whether its Iff() or Iif(), so check the syntax. Also I'm
not sure you need the [ ] around the field name.

 

[Dan Lavish]

Why do you use a field for the due date? You can simply create a calculated
control that does this.
calculate it as:
   =Iff(Date > GetBusinessDay([Request_Date], 5), "OVERDUE",
GetBusinessDay([Request_Date], 5))

you may have to change the afterupdate event to recalculate the control, if
this doesn't work automatically.

regards,
Tieske

PS. I never know whether its Iff() or Iif(), so check the syntax. Also I'm
not sure you need the [ ] around the field name.

I have a request date field and a reply due date field on my form with
the code listed below on the request date after update event i was
wondering if there is a way to update this daily because after five
days its over due the problem is the only way to change it from a date
to overdue is too reenter the date it also shows up on a report so any
help is cool thanks in advance

Private Sub Request_Date_AfterUpdate()
If Date >= GetBusinessDay(Me.Request_Date, 6) Then
   Me.Action_Overdue = "OVERDUE"
Else
   Me.Action_Overdue = GetBusinessDay(Me.Request_Date, 5)
End If
End Sub

' the getBusinessday is a module that skips business days and holidays
thanks to some one else!

i use a control because i export the data sheet to excel

 

[Tieske]

Hi Dan,

just set the ControlSource of the control to the formula I sent earlier,
that should do it.

regards,
Tieske

Why do you use a field for the due date? You can simply create a
calculated
control that does this.
calculate it as:
=Iff(Date > GetBusinessDay([Request_Date], 5), "OVERDUE",
GetBusinessDay([Request_Date], 5))

you may have to change the afterupdate event to recalculate the control,
if
this doesn't work automatically.

regards,
Tieske

PS. I never know whether its Iff() or Iif(), so check the syntax. Also I'm
not sure you need the [ ] around the field name.

I have a request date field and a reply due date field on my form with
the code listed below on the request date after update event i was
wondering if there is a way to update this daily because after five
days its over due the problem is the only way to change it from a date
to overdue is too reenter the date it also shows up on a report so any
help is cool thanks in advance

Private Sub Request_Date_AfterUpdate()
If Date >= GetBusinessDay(Me.Request_Date, 6) Then
Me.Action_Overdue = "OVERDUE"
Else
Me.Action_Overdue = GetBusinessDay(Me.Request_Date, 5)
End If
End Sub

' the getBusinessday is a module that skips business days and holidays
thanks to some one else!

i use a control because i export the data sheet to excel