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

I haven't had to deal with this before, but wouldn't this be a good
application for a pair of UDFs that transform dates to and from USA format?

Arthur

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Erwin Craps
Sent: July 1, 2003 2:51 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] DSUM - full solution for those interested - RE:
subquery assistance


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
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com