count weekdays into friday (1 Viewer)

[fst]

one more question, if Monday's a holiday how would the coding be to add that into Friday's totals? I currently have a table called tbl_Holidays where field one is called "Holiday" and the other field is called "Date". I was trying to see if the coding arnelgp first posted for excluding holidays to include it in the If statement but I get an error due to the DCount line.

 

[arnelgp]

that is for you to tell.
what do you do on Monday holiday?
if you come Monday(holiday) and print, same as usual (print-out: fri-sunday).
tues printout (Monday date, regardless if it is holiday?).

 

[fst]

sorry for not being clear. so, if Monday's a holiday, and I come in Monday then print-out is fri-sun (the query looks in the tbl_holiday if any holiday exists).
tues printout would be is checking the tbl_Holiday and if exists, adds fri-mon

I suppose another example would be is thanksgiving and black Friday. come Monday when I run the query, it would look in the tbl_holiday and see 11/28/19 and 11/29/19 and count wed-sun since wed was the last known work day.
hopefully that makes a bit more sense

 

[arnelgp]

test this:

Public Function fncIsPrevious(dteFieldValue As Variant, ByVal dteReference As Date) As Boolean
    i As Integer
    Dim dteDate As Date
    
    fncIsPrevious = False
    
    If IsEmpty(dteFieldValue) Or IsNull(dteFieldValue) Then Exit Function
    
    i = -1
    
    Do While True
        dteDate = DateAdd("d", i, dteReference)
        If DCount("1", "tbl_holiday", "[date]=" & Format(dteDate, "\#mm\/dd\/yyyy\#")) = 0 Then
            If InStr(1, "Sat/Sun", Format(dteDate, "mmm")) = 0 Then
                Exit Do
            End If
        End If
        i = i - 1
    Loop
    fncIsPrevious = (dteFieldValue >= dteDate) And (dteFieldValue < dteReference)
End Function

 

[fst]

I get a compile error: statement invalid outside Type Block where this is selected (not highlighted)

i As Integer

 

[arnelgp]

sorry, replace it with:

Dim i As Integer

 

[fst]

if I add +5 days from todays date (9/24/19) and put in my tbl_holiday that 9/23/19 is a holiday, it is skipping Friday.

 

[fst]

I changed the I - 1 to I-3 and I think it works but need your input:

i = i - 3

Public Function fncIsPrevious1(dteFieldValue As Variant, ByVal dteReference As Date) As Boolean
    Dim i As Integer
    Dim dteDate As Date
    
    fncIsPrevious1 = False
    
    If IsEmpty(dteFieldValue) Or IsNull(dteFieldValue) Then Exit Function
    
    i = -1
    
    Do While True
        dteDate = DateAdd("d", i, dteReference)
        If DCount("1", "tbl_holidays", "[date]=" & Format(dteDate, "\#mm\/dd\/yyyy\#")) = 0 Then
            If InStr(1, "Sat/Sun", Format(dteDate, "mmm")) = 0 Then
                Exit Do
            End If
        End If
        i = i - 3
    Loop
    fncIsPrevious1 = (dteFieldValue >= dteDate) And (dteFieldValue < dteReference)
End Function

 

[fst]

ok nevermind, it didnt quite work. if Friday and Monday was a holiday, ie a 4 day weekend (I put 9/20 and 9/23 as a holiday), changing the 1 to a 3 also adds in Wednesday but should only be adding thurs-mon

 

[arnelgp]

then change this:

fncIsPrevious1 = (dteFieldValue >= dteDate) And (dteFieldValue < dteReference)

to:

fncIsPrevious1 = (dteFieldValue > dteDate) And (dteFieldValue < dteReference)

 

[fst]

so originally with
fncIsPrevious1 = (dteFieldValue >= dteDate) And (dteFieldValue < dteReference)
it returned 9/22 and 9/23

afte changing it to
fncIsPrevious1 = (dteFieldValue > dteDate) And (dteFieldValue < dteReference)
it returned only 9/23

 

[theDBguy]

ok nevermind, it didnt quite work. if Friday and Monday was a holiday, ie a 4 day weekend (I put 9/20 and 9/23 as a holiday), changing the 1 to a 3 also adds in Wednesday but should only be adding thurs-mon

Hi. Pardon me for jumping in... Since you "manually" run this process anyway, what's one more step to simply enter the number of days of data to grab like we discussed earlier, which you said wasn't "automated" enough, but I think is much simpler than what this seems to be turning out to be. Just my 2 cents...

 

[arnelgp]

ok, i see my error, put this back:

fncIsPrevious = (dteFieldValue >= dteDate) And (dteFieldValue < dteReference)

now replace: Format(dteDate, "mmm"), with Format(dteDate, "ddd")

 

[fst]

it returns only 9/23/19

 

[fst]

Hi. Pardon me for jumping in... Since you "manually" run this process anyway, what's one more step to simply enter the number of days of data to grab like we discussed earlier, which you said wasn't "automated" enough, but I think is much simpler than what this seems to be turning out to be. Just my 2 cents...

the end goal is to be able to run a task scheduler every morning then have the data linked to excel that runs a refresh macro that populates the data based for the prior workday.

 

[arnelgp]

i just tested it and it returns fri-mon (20-23).
do you have same code:

Public Function fncIsPrevious(dteFieldValue As Variant, ByVal dteReference As Date, ByVal holidayTable As String) As Boolean
    Dim i As Integer
    Dim dteDate As Date
    
    fncIsPrevious = False
    
    If IsEmpty(dteFieldValue) Or IsNull(dteFieldValue) Then Exit Function
    
    i = -1
    
    Do While True
        dteDate = DateAdd("d", i, dteReference)
        If DCount("1", "tbl_holiday", "[date]=" & Format(dteDate, "\#mm\/dd\/yyyy\#")) = 0 Then
            If InStr(1, "Sat/Sun", Format(dteDate, [COLOR="Blue"]"ddd"[/COLOR])) = 0 Then
                Exit Do
            End If
        End If
        i = i - 1
    Loop
    fncIsPrevious = (dteFieldValue >= dteDate) And (dteFieldValue < dteReference)
End Function

my SQL:
SELECT tbl_test.date, tbl_test.day
FROM tbl_test
WHERE (((fncIsPrevious([tbl_test.date],#9/24/2019#,"tbl_holiday"))=True));

 

[fst]

let me double check

 

[fst]

this is what I have:

Public Function fncIsPrevious1(dteFieldValue As Variant, ByVal dteReference As Date) As Boolean
    Dim I As Integer
    Dim dteDate As Date
    
    fncIsPrevious1 = False
    
    If IsEmpty(dteFieldValue) Or IsNull(dteFieldValue) Then Exit Function
    
    I = -1
    
    Do While True
        dteDate = DateAdd("d", I, dteReference)
        If DCount("1", "tbl_holidays", "[date]=" & Format(dteDate, "\#mm\/dd\/yyyy\#")) = 0 Then
            If InStr(1, "Sat/Sun", Format(dteDate, "ddd")) = 0 Then
                Exit Do
            End If
        End If
        I = I - 1
    Loop
    fncIsPrevious1 = (dteFieldValue >= dteDate) And (dteFieldValue < dteReference)
End Function

and this is in my query:

where   fncIsPrevious1(test.Completions, Date()+5,"tbl_holidays") = True

edit: to add in the "tbl_holidays" as I see you added it as a string but I only get 9/23/19 as a return result?

 

[theDBguy]

the end goal is to be able to run a task scheduler every morning then have the data linked to excel that runs a refresh macro that populates the data based for the prior workday.

I see. Thanks for the clarification. Good luck!

 

[fst]

I copied your code and also changed the where to show #9/24/2019# and somehow still only get 9/23? maybe theres something up with a date format in either the holiday table and/or the table its pulling from