Date Formatting w/holidays and weekends 2

[kentwoodjean]

Having a few date issues based on some queries I need to combine for a report.
I have 2 append queries that run each work day, Monday thru Friday. They provide me data based on the previous business day’s results and result in a weekly report. For the date feature, I needed to create an expression as follows to get my previous days results in inventory.

Weekend: [Closed Dt]-1-Weekday([Closed Dt])

This works fine until I run into the weekend or a 3 day holiday as I did with Good Friday. How should this be formatted to account for those 3 day holiday, or 2 day regular weekends?

 

[MoLaker]

You are going to have to create a function and call the function from your query to do you computations. Even that will require a means to store holidays (table) so your function can check for a holiday. I suggest a table rather than attempting to place the holiday dates in your function.

Totally untested function. As noted, it requires you to have a table named "Holidays" which contains a field called "HoliDates" with your holidays entered. Another advantage of using something like this is you can enter a date any time a day becomes a non-workday, such as a snow day, perhaps, or JFK is shot again.

Public Function LastWorkDay(Dt As Date) As Date
    [green]' Dt is the date in question[/green]
    Dim WD As Integer
    WD = Weekday(Dt)
    If WD = 2 Then
        [green]' if Monday, subtract 3 to reflect Friday[/green]
        LastWorkDay = Dt - 3
    Else
        [green]' else subtract 1 to reflect day before[/green]
        LastWorkDay = Dt - 1
    End
    [green]'assuming table named Holidays contains field named HoliDate containing holiday dates
    ' looped to cover 2 day holidays (Thanksgiving??)
    ' or any other reason for day off[/green]
    Do While IsDate(DLookup("HoliDate", "Holidays", "HoliDate = #" & LastWorkDay & "#")) = True
        [green]'DLookUp looks for holiday falling on the LastWorkDay[/green]
        LastWorkDay = LastWorkDay - 1
    Loop
End Function

 

[kentwoodjean]

I hate to appear totally ignorant, but writing/using functions is not something that I typcially do so I have 2 questions. I have created the Holiday table, and also the function as you have indicated above. Does this then replace the expression I created and therefore should remove it? 2nd dumb question. When I run the query that will then call out the function for me? Thanks in advance for your patience, but this helping me learn.

 

[lespaul]

Your query should now be:

Weekend: LastWorkDate([Closed Dt])

Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[MoLaker]

Jean, Leslie,

I won't be able to followup on this thread due to a personal situation. Leslie, do you mind helping Jean if more help is needed?

MoLaker

 

[lespaul]

no problem, hope everything works out, take care.

les

 

[kentwoodjean]

OK, I'm going to work on it this evening and will let you know how it all works out. Thanks for the input.