count weekdays into friday

[fst]

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:

where tbl.shipdate=Date()-1

 

[theDBguy]

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]

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

 

[theDBguy]

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

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]

yes, where if Monday then grab 'all' data (Friday-sunday) and if not Monday, but Tuesday-Friday to grab prior day.

 

[theDBguy]

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]

is it possible to be written as a query/sql or as a function?

 

[arnelgp]

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:

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 "

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]

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]

sorry i did not read post #3 to #5. late poster.

 

[arnelgp]

so my last code won't do it.
check this new 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

 

[fst]

I get a Run-time error 94: Invalid use of Null
at line:

fncIsPrevious = (d1 = DateAdd("d", -1, d2))

 

[arnelgp]

i edited the function, you may replace yours also.

 

[fst]

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]

is it possible to be written as a query/sql or as a function?

Hi. Earlier, in your first post, you had this idea:

where tbl.shipdate=Date()-1

So, if you're running this query everyday, I would probably just try something like:

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]

do you have previous data, why wait till Monday when you can adjust the computer date to previous Monday and test it.

 

[fst]

^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?

fncIsPrevious = False

I presume dl is the date in the field and if empty just resume/quit?

If IsEmpty(d1) Or IsNull(d1) Then Exit Function

 

[fst]

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]

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]

thanks for the clarification arnelgp.