Calculating a date in the future

[Leesha]

Hi,
I have a user who needs to calculate late fees if an invoice wasn't paid by the 10th of the month following their billing cycle. I have this set up so that [txtEndDate] is the last date of the billing cycle and [txtLateFeeDate] is the control that the late fee query looks to to calculate late fees for any payments after the date in [txtLateFeeDate]. The formula in [txtLateFeeDate] is =[txtenddate]+10. This is all working fine, till the user realized that there will be times when the banks are not open on the 10th of the month either due to Columbus Day Holiday, which is always the 2nd Monday of th month, or when the 10th of the month falls on a Saturday or a Sunday. Ugh!

I would to be able to create a conditional formula that does the following:

1. When the 10th falls on a Sunday as well as if Columbus Day is on the 10th the formula in [txtLateFeeDate] would be =[txtenddate]+11
2. When the 10th falls on a Saturday, the formula would be =[txtenddate]+12
3. If 1 or 2 is not the case the formula is =[txtenddate]+10

I had thought about creating a table that holds Columbus day dates for an infinite period to time but was hoping there was an easier formula, I'm not sure how to create the formula for 1 and 2.

Thanks!
Leesha

[HansV]

Is Columbus day the only holiday that you need to take care of?

[Leesha]

Hi Hans,
You're reading my mind! I went over that with her multiple times and she was firms it was only Columbus Day. So, for now it's just Columbus Day.
Thanks,
Leesha

[HansV]

I'd prefer to perform the calculations in a query, but if you prefer to do it in controls on the form:
- Add a text box txtTemp to the form and set its Control Source to

=[txtEndDate]+10

- Set the Visuble property of txtTemp to No. The end user doesn't need to see it.
- Change the Control Source of txtLateFeeDate to

=[txtTemp]+2*(Weekday([txtTemp])=7)+(Weekday([txtTemp])=1)+(Month([txtTemp])=10)*(Weekday([txtTemp])=2)

[Leesha]

Hi Hans,
Well first I'm glad I didn't try this on my own lol! The reason it's on a control vs a query is because there are multiple places in the code that look to the control and if things change down the line, as they always do, it' easier to change the control vs find all the queries.

I tested the code with a few dates. This is what I found with various txtEndDates:

1. 9/30/22 produced 10/11/22 - Perfect!
2. 10/31/22 produced 11/10/22 - Perfect!
3. 11/30/22 produced 12/8/22 - Should be 12/12/22
4. 12/31/22 produced 1/10/23 - Perfect!
5. 5/31/23 produced 6/8/22 - Should be 6/12/23
6. 9/30/23 produced 10/10/23 - Should be 10/11/23
7. 11/30/23 produced 12/9/23 - Should be 1/11/23
8. 1/30/24 produced 2/8/24 - Should be 2/12/24
9. 2/29/24 produced 3/9/24 - Should be 3/11/24

It seems like when the 10th falls on a Saturday it returns the 8th as the date and when the 10th falls on a Sunday it doesn't return the 11th. I checked to be sure that the date in [txtTemp] is always coming up with the 10th and it is.

I don't begin to know where to change it in the formula. Do you think this is even possible or should I have them manually enter the Late fee date? I hate to have them do that as there is too much room for user error.

Thanks!
Leesha

[HansV]

Sorry about that, I was testing in Excel instead of Access. Use

=[txtTemp]-2*(Weekday([txtTemp])=7)-(Weekday([txtTemp])=1)+(Month([txtTemp])=10)*(Weekday([txtTemp])=2)

[Leesha]

PERFECTION!! Thanks so much!