Update a Field based off of another Field in the same Table

[Seph]

Good day everyone,

I have two Fields in my InvoiceT. Namely InvoiceDate and InvoiceDueDate.

Data in the InvoiceDate field changes based on the certain situations.

What I'm trying to achieve is having the InvoiceDueDate field populate with the same date as the InvoiceDate field + 5 days.

I tried in the Default Value property, but that didn't work. Perhaps an after update?

Thanks!

 

[CJ_London]

you shouldn't really need the duedate field since it can be calculated as and when required

but if you must then you can use a calculated field but note that calculated fields cannot be indexed and I think cannot be used in joins (not tested)

or in vba on a form, in the invoicedate afterupdate event

calculation would be has you have it - invoicedate+5

 

[MajP]

Two questions
1. Is it really 5 days or more complex to removing weekends and holidays? You can search of this forum for more robust calculations.
2. Is it ever editable or 100% of the time no exceptions ever 5 days. Example if something comes up, someone needs more time, can you give them some grace period on the due dates. If so then you do need a field like you have it. You cannot use a calculated field in a query.

 

[Seph]

Two questions
1. Is it really 5 days or more complex to removing weekends and holidays? You can search of this forum for more robust calculations.
2. Is it ever editable or 100% of the time no exceptions ever 5 days. Example if something comes up, someone needs more time, can you give them some grace period on the due dates. If so then you do need a field like you have it. You cannot use a calculated field in a query.

Thank you gentleman/gentlewomen for your feedback.

@MajP

1. The more comprehensive calculation sounds really enticing. I was just going to manually check every time. Knowing now that it is possible, I will definently look into it. Thanks!

2. The general rule of thumb would be 5 days upon receival of invoice. I would like for it to be editable though, as like you said, situational.

I'd like this to happen on a Form level as I have two Forms (Job Invoice / Sales invoice) and they both need the InvoiceDueDate field.

Thanks!

 

[MajP]

1. If you want it to be editable then keep the due date field
2. You should be able to find a function that you can tailor, and I would apply that in the afterupdate event of the InvoiceDate on a form not in the table default value.
The problem with default value that only works when you enter a value. If you come back and edit the invoice date because you made a mistake it will not update the due date.
3. You can easily find a function that excludes weekends (or set to your companies work week). However, if you want to check for holidays too then you need to create a holiday table that you keep updated. This gets incorporated in your function. Someone on this forum probably has a good example. Often people will build it several years out. Holidays often do not align with the actual work day off. This year Xmas and New Years were on a weekend so different companies will have different policies on the actual day off.
4. So you will likely build (tweak) a custom function in the after update
me.DueDate = myAddFiveWorkDays(me.InvoiceDate)

where myAddFiveWorkDays is the function you write.

 

[The_Doc_Man]

Then here is another consideration. IF you do this by table-based computation, you won't be able to edit it. If you do this by query-based computation, the query will not be updateable. If you do this via a computation in the form, neither the table nor the query will suffer any barriers to being updated. So doing it at a form level is the way to go if you are going to do it at all.

 

[MajP]

Here are the functions

Public Function WorkDaysBetween(StartDate As Date, endDate As Date) As Integer
  Dim CurrentDay As Date
  CurrentDay = CDate(Int(StartDate))
  Do
    If Weekday(CurrentDay) <> vbSaturday And Weekday(CurrentDay) <> vbSunday And Not IsHoliday(CurrentDay) Then
      WorkDaysBetween = WorkDaysBetween + 1
    End If
    CurrentDay = CurrentDay + 1
  Loop Until CurrentDay > endDate
End Function
Public Function isWeekend(CurrentDay As Date) As Boolean
  'Adjust for your work schedule i.e. 4day work week
  If Weekday(CurrentDay) = vbSaturday Or Weekday(CurrentDay) = vbSunday Then isWeekend = True
End Function

Public Function IsHoliday(CurrentDay As Date) As Boolean
  IsHoliday = (DCount("*", "tbl_Holidays", "HolidayDate = #" & Format(CurrentDay, "mm/dd/yyyy" & "#")) = 1)
End Function

Public Function AddWorkDays(StartDate As Date, DaysToAdd) As Date
  Dim I As Integer
  Dim CurrentDay As Date
  CurrentDay = StartDate
  Do
    CurrentDay = CurrentDay + 1
    If Not IsHoliday(CurrentDay) And Not isWeekend(CurrentDay) Then I = I + 1
  Loop Until I = DaysToAdd
  AddWorkDays = CurrentDay
End Function

Here is the example table and field names to make it work
tbl_Holidays tbl_Holidays

HolidayID	HolidayDate	HolidayName
50​	1/2/2023​	New Years Day
51​	5/27/2023​	Memorial Day
52​	7/4/2023​	Independence Day
53​	9/2/2023​	Labor Day
54​	11/28/2023​	Thanksgiving Day
55​	11/29/2023​	Day After Thanksgiving Day
56​	12/24/2023​	Christmas Eve
57​	12/25/2023​	Christmas Day

Here is a test

debug.print AddworkDays(#12/30/2022#,5)
returns 1/9/2023

In the above example the 30th is Friday. The actual day off is the 2nd, and 7,8 are weeknds
30 Fri
31 Sat
1 Sun
2 hol
3 +1
4 +2
5 +3
6 +4
7 Sat
8 Sun
9 +5

 

[JonXL]

In line with above if you are going to store some additional value related to this, I'd make it the days allowed instead of the due date itself.

 

[JonXL]

I guess I read "can be overridden" to mean at the invoice level. So that would just take the one field. When users want to override the due date, they directly or indirectly update days due to get the date they want.

I am not sure I follow the idea of a separate table for this one attribute unless the default days due changes often and your record add process for invoices references that to get the initial days due value instead of setting the default in the invoices table's design. That would still only need a days due field on the invoices which gets overridden per the process above.