[AccessD] DSUM - full solution for those interested - RE: subquery assistance

Hi David

As you have specified datStartDate and datEndDate as date/time there
should really be no reason to use Format() wrapped in crosses (#) as
that just leaves you with the date/time value again.

If StartDate and EndDate are names of date/time fields of the table,
this should do as you compare date/time values with date/time values:

    strCriteria = "(CampusID=" & intCampus _
        & ") AND (StartDate > " datStartDate & ") " _
        & "AND (EndDate < " & datEnddate & ")"

/gustav


Public Function TotalHolidayWeeks(intCampus As Integer, datStartDate As Date, datEnddate As Date) As Integer

    'Calculates the number of weeks holiday for a campus between two given dates

    Dim strCriteria As String

    strCriteria = "(CampusID=" & intCampus _
        & ") AND ((StartDate) > #" & Format(datStartDate, "dd/mmm/yyyy") & "#) " _
        & "AND ((EndDate) < #" & Format(datEnddate, "dd/mmm/yyyy") & "#)"

    TotalHolidayWeeks = Nz(DSum("Weeks", "tblCourseHolidays", strCriteria), 0)

End Function