Solved

Date calculation based on date plus a foem data number

Posted on 2014-11-06
19
136 Views
Last Modified: 2014-11-07
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?
0
Comment
Question by:SteveL13
  • 9
  • 9
19 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40427217
Yes:

Me!txtDate2.Value = DateAdd("d", Nz(Me!txtSchedFreqDays, 0), DateAdd("m", Nz(txtSchedFreqMonths, 0), Me!txtDate1))

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.
                datDate = DateAdd("d", 2, datDate)
            Case vbSunday
                ' Skip Sunday.
                datDate = DateAdd("d", 1, datDate)
            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.
                        datDate = DateAdd("d", 1, datDate)
                    End If
                    On Error GoTo 0
                End If
        End Select
    Loop Until booWorkday = True
    
    DatePassNonWorkdays = datDate

End Function

Open in new window

/gustav
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40427221
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 adatDays()  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
        ReDim adatDays(lngDays - 1)
        ' 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)
            adatDays(lngDays) = avarDays(clngDimFieldOne, lngDays)
        Next
    End If
        
    GetHolidays = adatDays()
    
End Function

Open in new window


The final expression would look:

Me!txtDate2.Value = DatePassNonWorkdays(DateAdd("d", Nz(Me!txtSchedFreqDays, 0), DateAdd("m", Nz(txtSchedFreqMonths, 0), Me!txtDate1)))

/gustav
0
 

Author Comment

by:SteveL13
ID: 40427269
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.

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

Expert Comment

by:Gustav Brock
ID: 40427836
> 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:

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

/gustav
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40427963
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
, DateAdd("d",Val([f11]),DateAdd("m",Val([f22]),[date1])) AS d1
, IIf(Weekday([d1])>5,DateAdd("d",(8-Weekday([d1])),[d1]),[d1]) AS date2
FROM a;

Open in new window

if f1, f2 are in a subform, then use  f11:Forms![_tutorial]!subformControl.Form!f1, and f22:Forms![_tutorial]!subformControl.Form!f2,
0
 

Author Comment

by:SteveL13
ID: 40428426
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)))

Open in new window


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

???
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40428443
Sorry, too fast. This is how it should read:

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

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

/gustav
0
 

Author Comment

by:SteveL13
ID: 40428465
Still not working with...

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

Author Comment

by:SteveL13
ID: 40428475
Error is "Object doesn't support this property or method".
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40428477
> 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
0
 

Author Comment

by:SteveL13
ID: 40428500
Am going to send you a stripped down copy.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40428509
Please, just obtain the name of the subform control holding the subform (it is not txtActualServiceStartDate ), then correct the expression above.

/gustav
0
 

Author Comment

by:SteveL13
ID: 40428520
I am so sorry I don't understand.  When you say "subform control" do you mean the main form name?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40428532
No. It is the name of the subform control (on the main form) holding the subform.

/gustav
0
 

Author Comment

by:SteveL13
ID: 40428571
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
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40428659
Oh, I see. You are in the subform. Then it is the other way round ... refer to the parent form:

Me!txtNextScheduleStartDate.Value = DatePassNonWorkdays(DateAdd("d", Nz(Me.Parent!txtSchedFreqDays, 0), DateAdd("m", Nz(Me.Parent!txtSchedFreqMonths, 0), Me!ActualServiceStartDate)))

/gustav
0
 

Author Comment

by:SteveL13
ID: 40428736
Amazing!  Thank you so much.
0
 

Author Closing Comment

by:SteveL13
ID: 40428737
Amazing!  Thank you so much.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40428754
You are welcome!

/gustav
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now