You will not be able to use the control's Default Value property in this case
because a default value is applied as soon as a new record is completed.
Here is an idea that might be useful for you.
Your rules are a bit incomplete. You state
If the Priority equals 0 and the Investigation Date is Blank then the
re-investigation would be blank.
But, you don't say what to do if Priority is 0 and Investigation date is not
Blank. So I will go with what I understand.
Note there is a function at the bottom of the post you can paste into a
standard module that will add only week days and not count holidays if you
create a holiday table as defined in the function. If you don't want to
consider holidays, just comment out the line that counts the holidays.
Use the Before Update event of the Priority control to populate the
Investigation Date control. That way, you can trap for an incorrect value in
priority.
Private Sub txtPriority_BeforeUpdate(Cancel As Integer)
Dim lngAddDays As Long
Select Case Me.txtPriority
Case 0
If IsNull(Me.txtInvestigationDate) Then
Me.txtReinspectDate = Null
Exit Sub
End If
Case 1
lngAddDays = 5
Case 2
lngAddDays = 10
Case 3
lngAddDays = 30
Case Else
MsgBox "Invalid Priority Code"
Cancel = True
Exit Sub
End Select
If Not IsNull(Me.InvestigationDate) Then
Me.txtReinspectDate = AddWorkDays(Me.InvestigationDate, lngAddDays)
End If
End Sub
'---------------------------------------------------------------------------------------
' 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
' : 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
' : tblHoliday and the field used is a Date field named Holidate
' : No time vaule should be in the Holidate field. Time values my
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
Dim dtmNextDate As Date
Dim dtmLowDate As Date
Dim dtmHighDate As Date
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]", "tblHoliday", "[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
--
Dave Hargis, Microsoft Access MVP
PennyB said:
I have a field called ReInspectionDate.
I need the default value of this field to be calculated on two other fields
(reason for default is they need the ability to put their own date as well):
The Default value needs to calculate as the following:
If the Priority equals 1 it would be Investigation Date + 5 (only want
weekdays)
If the Priority equals 2 it would be Investigation Date + 10 (same weekdays)
If the Priority equals 3 it would be Investigation Date + 30 (same weekdays).
If the Priority equals 0 and the Investigation Date is Blank then the
re-investigation would be blank.
I know I need an IF statement, but I don't know how to get it to account for
weekdays only.
Any help would be appreciated.
Thanks,
PennyB
