[AccessD] Date Comparison Problem

A.D.TEJPAL adtp at airtelbroadband.in
Thu Mar 30 12:04:52 CST 2006


 
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



  Rocky Smolin - Beach Access Software wrote:
  > Dear List:
  >
  > As I was writing this question a solution occurred to me.  But since I 
  > already spent all this time framing the question thought I'd post it 
  > anyway and add the answer to the end.
  >
  > I have a routine which works when regional setting is set to English 
  > (United States) and fails when regional settings are set to English 
  > (United Kingdom) .  The difference is in a date compare.
  >
  > The statement reads: "If datCurrentDate > Me.txtScheduleEndDate Then"
  >
  > When set to United States datCurrentDate contains 12/02/2005 and 
  > Me.txtScheduleEndDate contains 07/30/2006 based on making a break point 
  > at that statement and hovering the mouse over the two variables.  So the 
  > test fails, as it should.
  >
  > When regional setting is set to United Kindom datCurrentDate contains 
  > 02/12/2005 and Me.txtScheduleEndDate contains 30/07/2006 .  This time 
  > the test passes which it should not.
  >
  > Turns out that even though the text box shows 30/07/2006 it was not 
  > making the comparison correctly.  So I Dimmed another date 
  > datScheduleEndDate and changed the code to read:
  >
  >         datScheduleEndDate = Me.txtScheduleEndDate
  >         If datCurrentDate > datScheduleEndDate Then
  >
  >
  > And now it works.
  >
  > Solution in search of a problem.
  >
  > Thanks (for making me think this through) and regards,
  >
  > Rocky
  >

  -- 
  Rocky Smolin
  Beach Access Software
  858-259-4334
  www.e-z-mrp.com


More information about the AccessD mailing list