Date flag

[glasgowlad1999]

Hi There,



What is the best way to flag a record that is due within 3 days.

I have a date received lets say 11/23/2010 and it is due in 30 days. I want to flag that record in a datasheet when it is within 3 days. However, if it is due on the Monday. I need it to not count the weekends as days.

Thanks.

[RuralGuy]

Here's a function I use:

Public Function AdjWorkDays(dteStart As Date, _
                            intNumDays As Long, _
                            Optional blnAdd As Boolean = True) As Date
AdjWorkDays = dteStart
Do While intNumDays > 0
   If blnAdd Then
      '-- Adding WorkDays
      AdjWorkDays = AdjWorkDays + 1
   Else
      '-- Subtracting WorkDays
      AdjWorkDays = AdjWorkDays - 1
   End If
   If Weekday(AdjWorkDays, vbMonday) <= 5 Then
'-- Use the following code instead, if you have a "Holiday" table
'   If Weekday(AdjWorkDays, vbMonday) <= 5 _
      And IsNull(DLookup("[Holiday]", "tblHolidays", _
      "[HolDate] = " & Format(AdjWorkDays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l")))  Then
      intNumDays = intNumDays - 1
   End If
Loop
End Function

[glasgowlad1999]

Rural,

You are great.

One question AdjWorkDays = AdjWorkDays + 1 it this where I plus my 30 days?

so today + 30 - 5 days to flag it.

[RuralGuy]

YourDate: =AdjWorkDays([OriginalDate],30-5)
...should do it.

[emilyrogers]

Thank You! I have been trying to do this also! I havent implemented this into my database yet- where/how does the alert appear?

thanks

[RuralGuy]

Thank You! I have been trying to do this also! I havent implemented this into my database yet- where/how does the alert appear?

thanks

You would need to use a query, domain function or form to examine the calculated date and post the message.