Access query getting #error

[Mark27]

Hoping someone can help with this! I am receiving a #error when subtracting two dates when one or both of the date fields is blank. I created a function called Workdays which removes weekends & holidays. I am just not sure how to get rid of the #error. Below is the code that I am using.

Wrkdays1: Workdays([startDate),[endDate])

When both the startDate and endDate fields contain dates, it works perfectly....in stead of the #error, I need the field to be blank because I need to do additional queries off of this data.

Thanks

[Narender Sagar]

Hi Mark,
Create new fileds in query with if statement.
Something like :

EndDt: iif([EndDate] is null,0,[EndDate])

and similarly for StartDate...StartDt

StartDt: iif([StartDate] is null,0,[StartDate])

then subtract these fields.. and check

[jforbes]

Would you post your Workdays() Function?

[Mark27]

Hi Narender,
Thank you for responding! I followed your suggestion and had some issues but I finally figured it out. My issue was if I had a startdt but no enddt. I would get an answer like 30378. This makes sense because it is subtracting a date from nothing. My fix was I created another field and set anything to 0 that was great than 1,000.

Really appreciated your help on this!
Mark

[Mark27]

Hi jforbes,

I am using the workdays function from the link below that removes weekends & holidays. You can copy the information from the link into two modules one "week days" and one "work days". You will also need to create a table for holidays.

Once that information is loaded in your database, you can query off of a table that has a start date and end date by doing the follwing:

Wrkdays1: Workdays([Startdate],[Enddate])

https://msdn.microsoft.com/en-us/library/dd327646(v=office.12).aspx

[PhilOfWalton]

You may find the following helpful:-

Function EasterDate(Yr As Integer) As Date
     
    Dim Da As Integer
    Da = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
    EasterDate = DateSerial(Yr, 3, 1) + Da + (Da > 48) + 6 - ((Yr + Yr \ 4 + _
    Da + (Da > 48) + 1) Mod 7)
     
End Function


Public Function GoodFriday(Yr As Integer) As Date
    
    GoodFriday = DateAdd("d", -2, EasterDate(Yr))
    
End Function

Public Function EasterMonday(Yr As Integer) As Date
    
    EasterMonday = DateAdd("d", 1, EasterDate(Yr))
    
End Function

Public Function AscensionDay(Yr As Integer) As Date
    
    AscensionDay = DateAdd("d", 39, EasterDate(Yr))
    
End Function

Public Function FirstSpringHoliday(Yr As Integer) As Date

    FirstSpringHoliday = DateSerial(Yr, 5, ((7 - Weekday(DateSerial(Yr, 5, 7))) + 2) Mod 7)
    'DateSerial(Year(mydte), Month(mydte), ((7 - Weekday(DateSerial(Year(mydte), Month(mydte), 7))) + 2) mod 7)

End Function

Public Function LastSpringHoliday(Yr As Integer) As Date

    Dim TheDay As Integer

    TheDay = Day(DateSerial(Yr, 5, ((7 - Weekday(DateSerial(Yr, 5, 7))) + 2) Mod 7))     ' First Spring bank holiday

    TheDay = TheDay + 28
    If TheDay <= 31 Then
        LastSpringHoliday = DateAdd("d", 28, DateSerial(Yr, 5, ((7 - Weekday(DateSerial(Yr, 5, 7))) + 2) Mod 7))
    Else
        LastSpringHoliday = DateAdd("d", 21, DateSerial(Yr, 5, ((7 - Weekday(DateSerial(Yr, 5, 7))) + 2) Mod 7))
    End If

End Function

Public Function ChristmasDay(Yr As Integer) As Date

    ChristmasDay = DateSerial(Yr, 12, 25)
    
End Function

Public Function BoxingDay(Yr As Integer) As Date

    BoxingDay = DateSerial(Yr, 12, 26)
    
End Function

Public Function NewYearsDay(Yr As Integer) As Date

    NewYearsDay = DateSerial(Yr, 1, 1)
    
End Function

Public Function Scots2January(Yr As Integer) As Date

    Scots2January = DateSerial(Yr, 1, 2)
    
End Function

Public Function FirstAugustHoliday(Yr As Integer) As Date

    FirstAugustHoliday = DateSerial(Yr, 8, ((7 - Weekday(DateSerial(Yr, 8, 7))) + 2) Mod 7)
    'DateSerial(Year(mydte), Month(mydte), ((7 - Weekday(DateSerial(Year(mydte), Month(mydte), 7))) + 2) mod 7)

End Function

Public Function LastAugustHoliday(Yr As Integer) As Date

    Dim TheDay As Integer

    TheDay = Day(DateSerial(Yr, 8, ((7 - Weekday(DateSerial(Yr, 8, 7))) + 2) Mod 7))     ' First Spring bank holiday

    TheDay = TheDay + 28
    If TheDay <= 31 Then
        LastAugustHoliday = DateAdd("d", 28, DateSerial(Yr, 8, ((7 - Weekday(DateSerial(Yr, 8, 7))) + 2) Mod 7))
    Else
        LastAugustHoliday = DateAdd("d", 21, DateSerial(Yr, 8, ((7 - Weekday(DateSerial(Yr, 8, 7))) + 2) Mod 7))
    End If

End Function

Public Function ThanksgivingDay(Yr As Integer) As Date
    
    ThanksgivingDay = DateSerial(Yr, 11, 29 - Weekday(DateSerial(Yr, 11, 1), vbFriday))
    
 End Function

Phil