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

I always use  .... DateValue('" & myDate & "')......... when building 
SQL strings. 

That way it doesn't matter what the local date settings are and I 
don't need to worry about whether myDate is a string from a textbox 
or a value from a date variable.  I've never had it break yet.

On 1 Jul 2003 at 18:56, Arthur Fuller wrote:

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


-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System Support.