Erwin Craps
Erwin.Craps at ithelps.be
Tue Jul 1 01:50:56 CDT 2003
Hi Gustav/David. I'm not sure of that. Date do pose gigantic problems in everything that looks like a SQL statement in VBA. If the date structure of your country is not american you gonna get real trouble. SQL string in VBA need to be build with American dates. I believe there are ways to use ISO dates but than you need to format to. Never use half or long dates line 1 jun 2003. If the windows is set to other language it will crash. At least when using SQL server for this last issue. Erwin -----Oorspronkelijk bericht----- Van: Gustav Brock [mailto:gustav at cactus.dk] Verzonden: zondag 29 juni 2003 14:15 Aan: Access Developers discussion and problem solving Onderwerp: Re: [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 _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com