[AccessD] Date Comparison Problem

> Format(datCurrentDate, "m\/d\/yyyy")
I'd follow Gustav advice - this is the only(/the best) 100% safe
formatting to be used in Rocky's question context.
And its variations of course:

Format(datCurrentDate, "mm\/d\/yyyy")
Format(datCurrentDate, "m\/dd\/yyyy")
Format(datCurrentDate, "mm\/dd\/yyyy")

Others may work, may fail....

Shamil

----- Original Message ----- 
From: "A.D.TEJPAL" <adtp at airtelbroadband.in>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Thursday, March 30, 2006 11:41 PM
Subject: Re: [AccessD] Date Comparison Problem


> Rocky,
>
>    You can go ahead with Gustav's solution. No controversy there.
> Underlyng principle is same.
>
>    I have requested Gustav to test out regarding non-ambiguous format,
> i.e. "dd-mmm-yyyy" also, as it has certain merits as explained in my post
> to him.
>
> Best wishes,
> A.D.Tejpal
> ----------------
>
>  ----- Original Message ----- 
>  From: Rocky Smolin - Beach Access Software
>  To: Access Developers discussion and problem solving
>  Sent: Thursday, March 30, 2006 23:44
>  Subject: Re: [AccessD] Date Comparison Problem
>
>
>  A.D.:
>
>  Thanks for the reply.  That clears it up.  And you're right, I won't
>  have control over the user's settings.  Do you see any problem with
>  Gustav's approach of
>
>  Format(datCurrentDate, "m\/d\/yyyy")
>
>  as a general purpose solution?
>
>  Regards,
>
>  Rocky
>
>
>
>  A.D.TEJPAL wrote:
>  >
>  > Rocky,
>  >
>  >     If direct concatenation of date variable in a string is attempted,
> it gets embedded as per the short date format governed by regional
> settings on the particular computer - leading to problems where the
> settings happen to be ambiguous (using mm instead of mmm) and in deviation
> from US conventions.
>  >
>  >     For example, if the regional setting for short date is "dd-mm-yy",
> the value held by date variable will get embedded in the SQL string as
> "dd-mm-yy". However, at the query execution stage, this part of SQL gets
> interpreted as "mm-dd-yy" (i.e. day part is seen as month part and month
> part is seen as day part), leading to unexpected results.
>  >
>  >     There would have been no problem, if the system setting were
> non-ambiguous (e.g. "dd-mmm-yyyy"). However, the developer may not always
> be in a position to dictate the settings over user's computers
>  >
>  >     In order to eliminate the scope for such a pitfall (despite varying
> regional settings on different computers), the date value should always be
> embedded explicitly in a non-ambiguous manner (leaving no scope for
> confusion between the values meant for day part and month part
> respectively). This is done by applying the Format() function before
> concatenating the date values with main SQL string.
>  >
>  >     Sample given below demonstrates the concept. (TDate is a date type
> field in table T_Test, while StartDate & FinDate are variables holding
> date values)-
>  >
>  > StrSQL = "DELETE * FROM T_Test"
>  > StrSQL = StrSQL & " WHERE TDate Between  #" & _
>  >                  Format(StartDate, "dd-mmm-yyyy") & "#"
>  > StrSQL = StrSQL & " AND #" & _
>  >                  Format(FinDate, "dd-mmm-yyyy") & "#;"
>  >
>  > Best wishes,
>  > A.D.Tejpal
>  >
>  >   ----- Original Message ----- 
>  >   From: Rocky Smolin - Beach Access Software
>  >   To: Access Developers discussion and problem solving
>  >   Sent: Thursday, March 30, 2006 22:33
>  >   Subject: Re: [AccessD] Date Comparison Problem
>  >
>  >
>  >   Well I spoke (or wrote) a little too soon.  There's another
> statement,
>  >   preceding the others which works right in US date format but not in
> UK
>  >   format.  The statement reads:
>  >
>  >       rstManpower.FindFirst "fldManpowerDate = #" & datCurrentDate & "#
>  >   AND " _
>  >           & "fldSkillID = " & !fldSkillID
>  >
>  >   datCurrentDate contains 01/12/2006 and !fldSkillID = 8
>  >
>  >   If .Nomatch = True then it adds the record and cycles back to the
>  >   .FindFirst where .Nomatch should be False.  But it comes back True
> and
>  >   adds another record in an endless loop.  Even if I do a
>  >   rstManpower.Requery, it fails.  I looked in the Manpower table and
> the
>  >   records exists.
>  >
>  >   It works correctly when Windows is in US date format and fails in UK
> format.
>  >
>  >   Can anyone see what's wrong with this FindFirst statement?
>  >
>  >   MTIA,
>  >
>  >   Rocky
>  >
>  <<  SNIP  >>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com