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

Hi Erwin

You are correct, I was too fast, sorry.

David, the trap is that sometimes (for certain local settings) date
comparisons will work, sometimes (for some other local settings) they
will not because Access - when building the string - will replace
datStartDate and datEndDate with a string representation based on the
local settings which the SQL tries to interpret wearing US glasses.

To repeat my own posting from last year:

---

Date: Tue, 06 Aug 2002 09:36:46 +0200
From: Gustav Brock <gustav at cactus.dk>
Subject: Re: [AccessD] date/time format SOLVED

Hi Bob

For completeness, here's a function we use when building SQL strings
with dates to save a lot of typing.

It uses "the Lembit method" with escape characters to ignore local
settings of date/time separators which otherwise will fool the Format
function.

<code>

Public Function StrDateSQL(ByVal dat As Date) As String

' Formats full string of date/time in US format for SQL.
' Overrides local (non US) settings for date/time separators.
' Example output:
'
'   #08/16/1998 04:03:36 PM#
'
' 1999-10-21. Cactus Data ApS, CPH.

  StrDateSQL = Format(dat, "\#mm\/dd\/yyyy hh\:nn\:ss AM/PM\#")

End Function

</code>

---

Thus your code should be modified using this:

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

Sorry for the confusion.

/gustav


> 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