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

 

[Pat Hartman]

I would store the "+" days value in a table. Which table depends on your business rules. If it is a company standard, You can make a table to hold the single value and then give the users a form where they can change the value and not have to call you, the programmer to do it for them. If the number of days depends on the customer, then you would store it in the customer table although you would probably have a default of 5, this would allow you to set a different value for specific customers and leave the default of 5 for everyone else. If you have different types of customers such as commercial and residential, then you could store the number of days value in the customer type table. There are lots of options.

If the calculation is a simple + the days, then I would calculate it on the fly in the query. If you need to exclude weekends and holidays, I would build a function to do the calculation and still do the calculation on the fly. If you want to be able to override the calculated result, that is the ONLY case where I would store the calculated value and in this case, I would do the calculation in the form in the AfterUpdate event of the InvoiceDate.

 

[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.

 

[Pat Hartman]

If the due date can be overridden, you have to store the due date. Otherwise, you shouldn't store either. You should be able to obtain the number of days from a different source as I outlined above. I offered three different options. It really depends on the business rules. You just shouldn't be storing it here at all

 

[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.

 

[Pat Hartman]

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.

There are often more than one constant that an app needs to store so it is not as horrible a thought as you seem to think, but the point is, if it is stored in a table, the users can change it if necessary and who is to say it is ALWAYS only one value. I gave two other options where the days would be stored in different existing tables depending on the actual business rules. If the + days value is stored in code, a programmer needs to be called upon to change it. That is fine in an organization where there are programmers on staff but not so fine in small organizations where the app in use was built by a consultant. Would I modify an application to take a single, hard-coded value and put it in a table, probably not unless when I was called in to change it I found out that is more variable than initially thought. But would I build it that way were I building the application? In a heartbeat. I don't need to make people dependent on me to make changes like this and I don't need to make users who hired me as a consultant have to call in me or someone else to fix it for them.

Since we agree that only storing only ONE value - either the increment or the result is correct - can we possibly consider the fact that storing the result would be more efficient given that it eliminates the need for a calculation in every query/form/report and it can be indexed making it more efficient to search?