Solved ADO ListBox and Filter

[ProgramRasta]

I didn’t quite get at the time what you meant by making the form non-updateable, however, I do now!

This is quite problematic, I only illustrate the results of the duration in one field on the main form.

From researching online, the only solutions I have come across is to either make a temporary table or using a domain aggregate function such as a DLookup to look up the value I need from a query.

Quite frustrating considering the form is based on one table and there is only one calculated field in the record source and that is only there for illustrative purposes.

 

[CJ_London]

In that case use your UDF based on the SQL - your udf should look like this

Function WorkDays(SDate As Date, EDate As Date) As Long
Dim Hols As Long
Dim maxDur As Long
Dim stWD As Integer
Dim fullWE As Long
Dim balDays As Long
Dim hasSat As Boolean
Dim hasSun As Boolean

    Hols = DCount("*", "tblHoliday", "HolidayDate BETWEEN #" & Format(SDate, "mm/dd/yyyy") & "# AND #" & Format(EDate, "mm/dd/yyyy") & "# AND weekday(holidaydate,2) not in (6,7)")
    maxDur = EDate - SDate
    stWD = Weekday([SDate], 2) - 1
    fullWE = (maxDur \ 7)
    balDays = (maxDur Mod 7)
    hasSat = stWD <= 5 And stWD + balDays - 1 >= 5
    hasSun = stWD <= 6 And stWD + balDays - 1 >= 6
    WorkDays = maxDur - (fullWE * 2) + hasSat + hasSun - Hols

End Function

 

[ProgramRasta]

In that case use your UDF based on the SQL - your udf should look like this

Function WorkDays(SDate As Date, EDate As Date) As Long
Dim Hols As Long
Dim maxDur As Long
Dim stWD As Integer
Dim fullWE As Long
Dim balDays As Long
Dim hasSat As Boolean
Dim hasSun As Boolean

    Hols = DCount("*", "tblHoliday", "HolidayDate BETWEEN #" & Format(SDate, "mm/dd/yyyy") & "# AND #" & Format(EDate, "mm/dd/yyyy") & "# AND weekday(holidaydate,2) not in (6,7)")
    maxDur = EDate - SDate
    stWD = Weekday([SDate], 2) - 1
    fullWE = (maxDur \ 7)
    balDays = (maxDur Mod 7)
    hasSat = stWD <= 5 And stWD + balDays - 1 >= 5
    hasSun = stWD <= 6 And stWD + balDays - 1 >= 6
    WorkDays = maxDur - (fullWE * 2) + hasSat + hasSun - Hols

End Function

Thanks for this, I actually have identical code for the UDF. Something I done right!

appreciate all your help getting me over the line

 

[CJ_London]

I checked and this does not drop the holiday offset

good luck with your project