[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  >>