count weekdays into friday (1 Viewer)

fst

Registered User.
Local time
Today, 16:44
Joined
Apr 9, 2018
Messages
46
I searched around but did not find a solution or something similar to what i'm working with. what im trying to do is whether it would be doable in a query or better off in vba. the dataset i'm working with is a day old, meaning it'll always be yesterday's date and prior. and the work week is a mon-fri
this is a 2 part question, the first question is:
if today is a Monday, how would the coding be to include the weekend into Friday? also Monday holidays like labor day would be treated as a weekend so it'll get included in the Friday total as well. for that I was thinking of having a table strictly as holidays as an exception.

the second question is pulling prior day, would it basically be:
PHP:
where tbl.shipdate=Date()-1
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:44
Joined
Oct 29, 2018
Messages
21,358
Hi. To your first question, I am not sure I follow. Please remember, we can't see what you're seeing, so it's sometimes hard to understand a question without the context. However, I do agree with using a separate table to list the Holidays. And to your second question, that should work for dates fields without time components and you want exactly yesterday's records. Otherwise, you might have to play with using "less than or equal to" (<=). Cheers!
 

fst

Registered User.
Local time
Today, 16:44
Joined
Apr 9, 2018
Messages
46
I don't have the coding down currently, I apologize for that. so where I work, the business week is Monday through Friday but there is work that gets done on Saturdays and sundays but those numbers gets added into Friday's numbers.
so come Monday when I run my database, I want to be able to run a report/query for shipments for example for all shipments on Friday (which would include Saturday and sunday) as that was the last workday. then on Tuesday when I run my report/query it'll run the prior workday which is Monday, and etc. hopefully that makes a bit more sense:eek:
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:44
Joined
Oct 29, 2018
Messages
21,358
I don't have the coding down currently, I apologize for that. so where I work, the business week is Monday through Friday but there is work that gets done on Saturdays and sundays but those numbers gets added into Friday's numbers.
so come Monday when I run my database, I want to be able to run a report/query for shipments for example for all shipments on Friday (which would include Saturday and sunday) as that was the last workday. hopefully that makes a bit more sense:eek:
Okay, if I understand your scenario, then there's no need to worry too much about the day. If it's Monday, then grab "all" data from last Friday. If it's not Monday, then only grab the data from yesterday. Correct?
 

fst

Registered User.
Local time
Today, 16:44
Joined
Apr 9, 2018
Messages
46
yes, where if Monday then grab 'all' data (Friday-sunday) and if not Monday, but Tuesday-Friday to grab prior day.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:44
Joined
Oct 29, 2018
Messages
21,358
yes, where if Monday then grab 'all' data (Friday-sunday) and if not Monday, but Tuesday-Friday to grab prior day.
Sounds simple enough. Please let us know how it goes...
 

fst

Registered User.
Local time
Today, 16:44
Joined
Apr 9, 2018
Messages
46
is it possible to be written as a query/sql or as a function?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:44
Joined
May 7, 2009
Messages
19,169
I think it is best to use function and call it in your query.
you need to create a table for those regular holidays (tblHolidays):

holidayName (text)
holidayDate (date)

to count excluding holidays, pass the starting date, and ending date and the holiday table name to a function:
Code:
public function fncCountWeekdays(dStart As Date, dEnd As Date, sHolidayTable As String)
dim i as Date
dim count as long
For i = dStart to dEnd
    if Instr(1, "Sat/Sun", Format(i,"ddd"))=0
        'not Saturday or sunday
        'check if the date falls on a holiday
        if nz(dcount("1", sHolidayTable, "holidayDate=" & Format(i,"\#mm\/dd\/yyyy\#"), 0) = 0 then
            count = count + 1
        end if
    end if
next i
fncCountWeekdays=count
end function

same for your query criteria: "where tbl.shipdate=Date()-1 "

Code:
public function fncPrevDay(byval dDate as date, sHolidayTable as string, optional iDayToCut as integer = 1) as date
dDate = dDate - iDayToCut
Do While True
    If Instr(1, "Sat/Sun", Format(dDate, "ddd") = 0 Then
        If Dcount("1", sHolidayTable, "holidayDate=" & Format(dDate,"\#mm\/dd\/yyyy\#"), 0) = 0 then
            exit do
        End If
    End If
    dDate = dDate - 1
Loop
fncPrevDay = dDate
End Function

the new criteria:

where tbl.shipdate = fncPrevDay(Date, "tblHolidays", 1)
 

fst

Registered User.
Local time
Today, 16:44
Joined
Apr 9, 2018
Messages
46
arnelgp, thanks, I did run across your posts and saw something like this but they were from what I thought, for excluding weekends. im fairly still new to coding please bare with me as I read and try and break down your code.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:44
Joined
May 7, 2009
Messages
19,169
sorry i did not read post #3 to #5. late poster.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:44
Joined
May 7, 2009
Messages
19,169
so my last code won't do it.
check this new code:
Code:
public function fncIsPrevious(d1 as variant, d2 as date) as Boolean
dim sDate as string
fncIsPrevious = false
If IsEmpty(d1) Or IsNull(d1) Then exit function
sDate = format(d2,"ddd")
if sDate = "Mon" then
    fncIsPrevious = (d1 >= dateadd("d", -3, d2) and (d1 <= dateadd("d", -1, d2)
else
     fncIsPrevious = (d1 = dateadd("d", -1, d2))
end if
end function

your query criteria would now be:

where fncIsPrevious(tbl.shipdate, Date()) = True
 
Last edited:

fst

Registered User.
Local time
Today, 16:44
Joined
Apr 9, 2018
Messages
46
I get a Run-time error 94: Invalid use of Null
at line:
Code:
fncIsPrevious = (d1 = DateAdd("d", -1, d2))
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:44
Joined
May 7, 2009
Messages
19,169
i edited the function, you may replace yours also.
 

fst

Registered User.
Local time
Today, 16:44
Joined
Apr 9, 2018
Messages
46
arnelgp, thanks! looks like it works. it's picking up yesterday's data. i'll follow up after this weekend when I run the query Monday, but this is greatly appreciated and thank you for your help!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:44
Joined
Oct 29, 2018
Messages
21,358
is it possible to be written as a query/sql or as a function?
Hi. Earlier, in your first post, you had this idea:
fst said:
where tbl.shipdate=Date()-1
So, if you're running this query everyday, I would probably just try something like:
Code:
WHERE tbl.ShipDate=Date()-[Enter Number of Days]
So, if it's Monday today, when you run the query and gets the prompt to enter the number of days, you could enter 3; otherwise, you would enter a 1. The bonus part is if it's the middle of the week but it was a Holiday the previous day, then you could enter a 2 in the prompt (if you wanted to count the Holiday work, if any).


 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:44
Joined
May 7, 2009
Messages
19,169
do you have previous data, why wait till Monday when you can adjust the computer date to previous Monday and test it.
 

fst

Registered User.
Local time
Today, 16:44
Joined
Apr 9, 2018
Messages
46
^true but that requires a manual input which the idea was to automate as much as I can without interruption. im still a noob, but arnelgp, can you elaborate on some of your line coding please?

what's going on here? is it just saying if it errors to not show a message?
Code:
fncIsPrevious = False

I presume dl is the date in the field and if empty just resume/quit?
Code:
If IsEmpty(d1) Or IsNull(d1) Then Exit Function
 

fst

Registered User.
Local time
Today, 16:44
Joined
Apr 9, 2018
Messages
46
do you have previous data, why wait till Monday when you can adjust the computer date to previous Monday and test it.

that is true, let me try that method.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:44
Joined
May 7, 2009
Messages
19,169
first statement set the function to return false.
second line test your field date if null, if it is, and since we already set the function to return false, the function will exit and will return the False.
 

fst

Registered User.
Local time
Today, 16:44
Joined
Apr 9, 2018
Messages
46
thanks for the clarification arnelgp.
 

Users who are viewing this thread

Top Bottom