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

Charlotte Foust cfoust at infostatsystems.com
Tue Jul 1 18:14:53 CDT 2003


I think you might have a problem if the data was being exchanged between
sources that used different date languages, since the myDate string
might not be recognized as a date under a different regional setting.

Charlotte Foust

-----Original Message-----
From: Stuart McLachlan [mailto:stuart at lexacorp.com.pg] 
Sent: Tuesday, July 01, 2003 3:07 PM
To: Access Developers discussion and problem solving
Subject: RE: [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.



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


More information about the AccessD mailing list