Need Some Help Please-how To *include Weekends And Holidays With Previous Work Day (1 Viewer)

[fst]

did a few searches and majority of the searches were ones excluding weekends and holidays. so im trying to put together a query where I need to pull prior business day (mon-fri). where im trying to figure out is if I can do this in a query/SQL or in a module? basically I need to return prior day but if today is Monday for example, then prior business day would be Friday but I need to include the weekend data in with Friday. what's even more of a moneky wnrech in the gears are holidays. some like this past memorial day is on a Monday so if today was tueday then I need to get data from fri-mon. so I imagine I would need a table strictly for holidays correct (or maybe have a table with just weekends and holidays) and use an inner join with the table im pulling from? hopefully this make sense at all?

 

[jdraw]

basically I need to return prior day but if today is Monday for example, then prior business day would be Friday but I need to include the weekend data in with Friday.

??? Not sure what you want --sometimes include weekend, sometimes not???

You might search the forum for Business days or work days

This link may be helpful.

 

[fst]

sorry for the confusion. if today was Monday, I would want to pull the previous weekday, in this case Friday but also include saturday/sunday. however, if the Monday ends up being a holiday like memorial day, if I run my database coming back form work on Tuesday the query or module would look and realize Monday was a holiday so it would include it as part of Friday/Saturday/sunday. I would think I would need to create a tblHoliday to let the query/module know for the exception. does that make sense now?

 

[jdraw]

??? if today was Monday, I would want to pull the previous weekday, in this case Friday but also include saturday/sunday. ???
Maybe it's just me, but I don't understand??

See if this link is helpful.

 

[Uncle Gizmo]

Maybe it's just me, but I don't understand??

I see a lot of questions lately, that I don't understand. I used to ask leading questions to try and ferret out the OP's real question, but it's happening so often I've stopped engaging. I recently flounced out of a thread because my leading fell on deaf ears.

 

[Micron]

I think I get it. Depends on the answer to this - what do you want if you run it on Wednesday (or even Thursday, Friday)?

 

[jdraw]

Tony,
I agree that asking leading questions to get some context and/or detail seems to be getting fewer meaningful responses lately.

Micron,
You seem to be on a similar approach --we're guessing at what is required. Yours is a more direct question. I hope the response clarifies the requirement.

 

[Cronk]

I guess if we have been developing for a time, we learn to think of all the possibilities for an outcome. Over the years, there have been a number of times where I have

....
....
Case else
    msgbox "The developer was told this was never going to happen"
...

 

[fst]

I think I get it. Depends on the answer to this - what do you want if you run it on Wednesday (or even Thursday, Friday)?

sorry for not being clear. i'll try an example. so lets say my job does x number of widgets. the data is collected at end of the day. so today, I would be pulling yesterday's total number of widgets. I only want to pull prior weekday. any work done on a weekend would be counted for the Friday. so for example if today was Monday, and there were 5 widgets completed last Friday and 2 on Saturday, if I run my query I would get a total of 7 for Friday.
now the exception is if Monday is a holiday. I would return to work the following Tuesday and when I run my job, I would want to pull Friday to Monday but it'd count all under that one Friday.

 

[Micron]

AFAIK, there is no query for this because of the variables you have. IIF(Weekday(Date(),2)=1,Date()-3,Date()-1) should work for most Mondays but not when you run this on a Tuesday and Monday was a holiday. Nor when you run this on Monday and Friday was a holiday. My first thought is to simply use a form with 2 textboxes and their built in calendar control and just pick 2 dates to run against. Otherwise, you need a udf and a table of holidays. I have no such function written but I'll bet there is someone here who does.

 

[Cronk]

And if it were a 4 day weekend with Friday and Monday both holidays, you would want the production figures done for the following Tuesday as the sum of all production done from Thursday to Monday?

You could call a function in a query that would check for public holidays recorded in a table to set the date range for the query.

 

[The_Doc_Man]

Cronk's solution might be the way to do this, but let me give you my viewpoint. What you need is either a static calendar in a table or a function that looks at calendars in order to determine what was the previous working day.

A "static" method would be to have a predetermined calendar for which all days are marked as "working" or "non-working" and then you can query that table for the latest working day with a date less than (earlier than) today.

A "dynamic" method would look at a table of holidays but would be able to figure out weekends on its own based on the DatePart (Weekday) function.

You could build a date function for either of these approaches and then build it into a query, perhaps as a parameter, perhaps by concatenating text to build a dynamic SQL string. Then that date would be the point from which you take your data.

As noted by the others who have chimed in, this would not be a trivial problem to solve.

 

[Cronk]

Doc, I reckon it's straight forward. Starting from today and going back a day at a time, find the first day which is
not (Saturday or Sunday or is contained in the table of public holidays)

The date range for the query is between that day and the day before today.

 

[The_Doc_Man]

This kind of problem always straightforward if you set up your tables correctly.

 

[arnelgp]

you need to add a holiday table

tblHolidays(table)
holiday (date/time)
Event (short text)

fill the above table with the holidays you have on your country.

copy this function in a Module:

Public Function InReportDate(ByVal dteReportDate As Variant, ByVal TableDate As Variant) As Boolean
    Const HOLIDAY_TABLE As String = "[tblHolidays]"
    Const HOLIDAY_FIELD As String = "[holiday]"
    Dim tmpDate As Date
    Dim s As String
    Dim i As Byte
    i = 0
    If IsNull(dteReportDate) Or IsNull(TableDate) Then
        Exit Function
    End If
    If IsDate(dteReportDate) = False Or IsDate(TableDate) = False Then
        Exit Function
    End If
    'convert to proper date
    dteReportDate = CDate(dteReportDate)
    TableDate = CDate(TableDate)
    'day of week
    s = Format(dteReportDate, "ddd")
    If InStr("Fri/Sat/Sun", s) > 0 Then
        While s <> "Fri"
            dteReportDate = dteReportDate - 1   'DateAdd("d", -1, dteReportDate)
            s = Format(dteReportDate, "ddd")
        Wend
    End If
    'we only are concern if the report date
    'falls o a Friday
    If (s = "Fri") Then
        'check if monday and the day after monday is holiday
        'so we can include it in the report
        i = 2
        tmpDate = dteReportDate + i + 1  'DateAdd("d", i + 1, dteReportDate)
        While (tmpDate <> Date) And _
                (DCount("1", HOLIDAY_TABLE, HOLIDAY_FIELD & " = " & Format(tmpDate, "\#mm\/dd\/yyyy\#")) > 0)
            i = i + 1
            tmpDate = dteReportDate + i + 1     'DateAdd("d", i + 1, dteReportDate)
        Wend
    End If
    InReportDate = (TableDate >= dteReportDate) And _
                   (TableDate <= dteReportDate + i)
End Function

your Query syntax:

SELECT widget_name, widget_producted, date_field FROM yourProduceTable
WHERE InReportDate([report_date_here], [date_field])=True;

'///
[report_date_here] is the date of the report you need to produce.
[date_field] is a date field on your table.

 

[fst]

And if it were a 4 day weekend with Friday and Monday both holidays, you would want the production figures done for the following Tuesday as the sum of all production done from Thursday to Monday?

You could call a function in a query that would check for public holidays recorded in a table to set the date range for the query.

thank you everyone for chiming in
if Friday through Monday was a 4 day weekend returning to work on Tuesday, the total production figures would all be under Friday.

like arnelgp stated, I did create a holiday table but wasn't certain if this could be written up in sql/query or a combination of sql/query and also in a module. i'll first look at arnelgp's function and go from there and will keep you guys updated.

the_doc_man, I think the 'dynamic' method is more of what I am leaning towards.
im still a ways a newbie when it comes to access and sql language

 

[arnelgp]

if you got lost, you can always upload the production_table (where you got your reporting) + holiday table.
i'll check also.

 

[fst]

thanks for your assist and help with your expertise arnelgp. i'll let you know.

 

[fst]

so I tried the function and created a query similar to what you noted arnelgp. in the query, I wanted to see what results would happen if I put Tuesday (5/26/20) to simulate the date as that specific Tuesday but I got results from the same day. so I changed it to simulate the Monday, memorial day to see if it'd combine 5/22/20 to 5/25/20 but again only got results for 5/25/20?
here's the query I wrote:

SELECT e.MACO, e.RETAIL_DATE, e.MAPRDL
FROM qry_EPHLIB_SAAG99_Step1 e
where InReportDate((#5/25/2020#), e.RETAIL_DATE)=True;

I also attached a sample of the database i'm working on. the tbl_saag99 has all 2020 products and included the tbl_holiday table.

 

[arnelgp]

see the change in function, the HOLIDAY_FIELD is [Date] on your table holidays.
added static_date1 and static_date2, so it will be a little faster
since it skipped some of the codes.

see the 2nd step query in design view.