Date field updates date field

[JohnB]

I have one field called "date surveyed" and another called "Date Req'd".
When "Date Surveyed" is picked I need "Date Req'd" to be updated with that
date plus 21 days, not including any Saturdays or Sundays that occur between.
This date should be amendable by the user.

I then need to create a query which shows any records where the difference
between these dates is less than 21 days.

Any help much appreciated.

 

[Klatuu]

Use the After Update event of the [date surveyed] field to calculate and
populate the [Date reqd] field (thake the ' out of the field name before you
hurt yourself)

It would be like this:

Private Sub txtDateSurveyed_AfterUpdate()

Me.txtDateRequired = AddWorkdays(Me.txtDateSurveyed, 21)

Here is the function:

'---------------------------------------------------------------------------------------
' Procedure : AddWorkDays
' DateTime : 5/8/2006 14:24
' Author : Dave Hargis
' Purpose : Determines the date of a work day (mon-fri) a number of days
after or
' : before a given date.
' Arguments : OriginalDate The date you want to add or subtract from to
determine a
' : worday
' : DaysToAdd Either a positive or negative number of working
days
' : Saturdays and Sundays are not counted
' : Holidays can also be omitted using a holiday table. The table
should
' : Contain one row for each holiday. As written, the table is
named
' : tblHolidays and the field used is a Date field named Holidate
' : No time vaule should be in the Holidate field. Time values
may produce
' : Incorrect Results
'---------------------------------------------------------------------------------------
'
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Long) As Date
Dim lngAdd As Long
Dim lngDayCount As Long
Dim lngHolidayCount As Long

On Error GoTo AddWorkDays_Error

If DaysToAdd < 0 Then
lngAdd = -1
Else
lngAdd = 1
End If

AddWorkDays = OriginalDate

Do Until lngDayCount = DaysToAdd
AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
If Weekday(AddWorkDays, vbMonday) < 6 Then
If IsNull(DLookup("[holidate]", "tblHolidays", "[holidate] = #"
& _
AddWorkDays & "#")) Then
lngDayCount = lngDayCount + lngAdd
End If
End If
Loop

AddWorkDays_Exit:
On Error GoTo 0

Exit Function

AddWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddWorkDays of Module modDateFunctions"
GoTo AddWorkDays_Exit

End Function

*************************************************
Now, for your query, you can use the other funtion below to filter for
records where the difference is less than 21 in the query's WHERE clause:

WHERE CalcWorkDays([DateSurveyed], [DateRequired]) < 21

 

[Maarkr]

see if you can figure it out from this code... if not reply back.

http://www.mvps.org/access/datetime/date0012.htm