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