Date calculation based on date plus a foem data number

I have a form that has a date1 field.  When this date field is filled in I have another date2 field that I want to populate based on the 1st date field + a field named txtSchedFreqDays.

But there is another field on the form named txtSchedFreqMonths.   In the event this field has a values entered the the formula would be 1st date field + a field named txtSchedFreqMonths.

Either txtSchedFreqDays OR txtSchedFreqMonths will have a number value.  Never both.

But here's the catch... in both cases I never want the date2 field to be populated with a weekend date.  Always move to the following Monday.

Can this be done?
Gustav Brock

Yes:

To skip a weekend/Holiday, use this function:
``````Public Function DatePassNonWorkdays( _
ByVal datDate As Date, _
Optional ByVal booWorkOnHolidays As Boolean) _
As Date

'   Returns next workday following datDate.
'   2014-11-03. Cactus Data ApS, CPH

Dim lngDays     As Long
Dim lngHoliday  As Long

Dim booWorkday  As Boolean

Do
Select Case Weekday(datDate)
Case vbSaturday
' Skip weekend.
Case vbSunday
' Skip Sunday.
Case Else
If booWorkOnHolidays = True Then
' Don't check for holidays.
booWorkday = True
Else
' Check for holidays to skip.
' Ignore error when using LBound on an unassigned array.
On Error Resume Next
lngHoliday = LBound(GetHolidays(datDate, datDate))
If Err.Number > 0 Then
' Not a holiday.
booWorkday = True
Else
' Skip the holiday.
End If
On Error GoTo 0
End If
End Select
Loop Until booWorkday = True

DatePassNonWorkdays = datDate

End Function``````
/gustav
Forgot this function to retrieve the Holidays:
``````Public Function GetHolidays( _
ByVal datDate1 As Date, _
ByVal datDate2 As Date, _
Optional ByVal booDesc As Boolean) _
As Date()

'   Finds the count of holidays between datDate1 and datDate2.
'   The holidays are returned as an array of dates.
'   DAO objects are declared static to speed up repeated calls with identical date parameters.
'   2014-10-03. Cactus Data ApS, CPH

' The table that holds the holidays.
Const cstrTable             As String = "tblHoliday"
' The field of the table that holds the dates of the holidays.
Const cstrField             As String = "HolidayDate"
' Constants for the arrays.
Const clngDimRecordCount    As Long = 2
Const clngDimFieldOne       As Long = 0

Static dbs              As DAO.Database
Static rst              As DAO.Recordset

Static datDate1Last     As Date
Static datDate2Last     As Date

Dim avarDays    As Variant

Dim strSQL      As String
Dim strDate1    As String
Dim strDate2    As String
Dim strOrder    As String
Dim lngDays     As Long

If DateDiff("d", datDate1, datDate1Last) <> 0 Or DateDiff("d", datDate2, datDate2Last) <> 0 Then
' datDate1 or datDate2 has changed since the last call.
strDate1 = Format(datDate1, "\#yyyy\/mm\/dd\#")
strDate2 = Format(datDate2, "\#yyyy\/mm\/dd\#")
strOrder = Format(booDesc, "\A\s\c;\D\e\s\c")

strSQL = "Select " & cstrField & " From " & cstrTable & " " & _
"Where " & cstrField & " Between " & strDate1 & " And " & strDate2 & " " & _
"Order By 1 " & strOrder

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

' Save the current set of date parameters.
datDate1Last = datDate1
datDate2Last = datDate2
End If

lngDays = rst.RecordCount
If lngDays = 0 Then
' Leave adatDays() as an unassigned array.
Else
' As repeated calls may happen, do a movefirst.
rst.MoveFirst
avarDays = rst.GetRows(lngDays)
' rst is now positioned at the last record.
For lngDays = LBound(avarDays, clngDimRecordCount) To UBound(avarDays, clngDimRecordCount)
Next
End If

End Function``````

The final expression would look:

/gustav

I can't compile on:  DatePassNonWorkdays

I have copy/pasted your 2nd function as a module.

And my final expression now is:  (note that I forgot that the "txtSchedFreqDays" and "txtSchedFreqMonths" are on the main form.  The "txtActualServiceStartDate" and "txtNextScheduleStartDate" are on a subform.

> I can't compile on:  DatePassNonWorkdays

What does "on" mean? The functions both compile.

Maybe it's your expression? For a subform, you must use the name of the subform control, not the name of the form. Thus:

/gustav
Try this as a query: (excluding weekend days), starting on Monday
If a sample database is included, I could have used your field names. Hope that helps.
table a( dd) - dd is your date1 field
form:_tutorial (dd, f1, f2) - f1=txtSchedFreqDays, f2=txtSchedFreqMonths
A query as a record source.
``````SELECT a.dd AS date1, Val([Forms]![_tutorial]![f1]) AS f11
, Val([forms]![_tutorial]![f2]) AS f22
FROM a;``````
if f1, f2 are in a subform, then use  f11:Forms![_tutorial]!subformControl.Form!f1, and f22:Forms![_tutorial]!subformControl.Form!f2,

Gustav,

Two things...  I did not have the first module in the database so now I have no problem compiling.  But when using this...

``Me!txtNextScheduleStartDate.Value = DatePassNonWorkdays(DateAdd("d", Nz(Me!Forms!txtActualServiceStartDate.Form!txtSchedFreqDays, 0), DateAdd("m", Nz(Forms!txtActualServiceStartDate.Form!txtSchedFreqMonths, 0), Me!txtActualServiceStartDate)))``

I get an error message..." Microsoft Access can't find the field 'Forms' referred to in your expression."

???
Sorry, too fast. This is how it should read:

I seriously doubt your subform controls (those holding the subforms) are named "txtSomething"

/gustav

Still not working with...

Error is "Object doesn't support this property or method".
> I seriously doubt your subform controls (those holding the subforms) are named "txtSomething"

I convinced that txtActualServiceStartDate is a textbox, not a subform control.

/gustav

Am going to send you a stripped down copy.
Please, just obtain the name of the subform control holding the subform (it is not txtActualServiceStartDate ), then correct the expression above.

/gustav

I am so sorry I don't understand.  When you say "subform control" do you mean the main form name?
No. It is the name of the subform control (on the main form) holding the subform.

/gustav

Attached because I still don't understand.

Please open the d/b.  Go to [Customer Schedules] from the Main Menu.

Enter 11/4/2014 in the field named Actual Service Start Date.

Then the date that populates Next Sched Start Date should be 30 days after Actual Service Start Date but not allow for holidays or weekends.

Again, sorry I'm so confused.
Copy.accdb
Gustav Brock

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.