[AccessD] ADO Find, serious issue with international dates

Hi A.D.

Thanks.

This makes me believe that the optimum general format would be "yyyy/mm/dd". ADO and DAO and JET SQL respect this format and so do most (all?) modern SQL server engines.
Could anyone point to a scenario where this will _not_ work?

/gustav

>>> adtp at hotmail.com 29-01-2007 18:50 >>>
Gustav,

    Your findings are confirmed. The aberrations occur when day part of the date argument is <= 12, providing scope for dual interpretation. You deserve all-round thanks for drawing attention to this important issue.

    Based upon a series of tests on a computer having regional settings in format dd-mm-yyyy, the conclusions are summarized below (this refers to date string formats used for concatenation of date variables into VBA strings):

    1 - Formats found successful both in DAO & ADO recordsets:
        (a) yyyy/mm/dd
        (b) dd/mmm/yyyy
        (c) mmm/dd/yyyy
        (e) yyyy/mmm/dd

        Note - 1 (b) to (c) above are in the context of English as the language setting.

    2 - Format found successful only in DAO recordsets:
        mm/dd/yyyy

    3 - Format found successful only in ADO recordsets:
        dd/mm/yyyy

    Apparently, certain long held views need to be revised.

Best wishes,
A.D.Tejpal
---------------

  ----- Original Message ----- 
  From: Gustav Brock 
  To: accessd at databaseadvisors.com 
  Sent: Monday, January 29, 2007 00:05
  Subject: [AccessD] ADO Find, serious issue with international dates


  Hi all

  Could someone with a non-US machine try to run this tiny test and please confirm?

  Create tblDates with these three entries of DateTime field named Friday (yyyy-mm-dd):

    2006-12-22
    2007-01-05
    2007-01-26

  Then run this sub:

  Public Sub AdoFindDate(ByVal datDate As Date)

    Const cstrTable As String = "tblDate"
    
    Dim cnn         As ADODB.Connection
    Dim rst         As ADODB.Recordset
    
    Dim strDate     As String
    
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    
    strDate = Format(datDate, "\#yyyy\/mm\/dd\#")
    With rst
      ' Open table as recordset.
      .ActiveConnection = cnn
      .CursorType = adOpenKeyset
      .Source = cstrTable
      .LockType = adLockOptimistic
      .Open
      
      .MoveFirst
      strDate = Format(datDate, "\#yyyy\/mm\/dd\#")
      Debug.Print strDate
      .Find "Friday >= " & strDate & "", , adSearchForward
      If .EOF = True Then
        Debug.Print , "No match"
      ElseIf .BOF = False Then
        Debug.Print , "Found", .Fields("Friday").Value
      End If
      
      .MoveFirst
      strDate = Format(datDate, "\#d\/m\/yyyy\#")
      Debug.Print strDate
      .Find "Friday >= " & strDate & "", , adSearchForward
      If .EOF = True Then
        Debug.Print , "No match"
      ElseIf .BOF = False Then
        Debug.Print , "Found", .Fields("Friday").Value
      End If
      
      .MoveFirst
      strDate = Format(datDate, "\#m\/d\/yyyy\#")
      Debug.Print strDate
      .Find "Friday >= " & strDate & "", , adSearchForward
      If .EOF = True Then
        Debug.Print , "No match"
      ElseIf .BOF = False Then
        Debug.Print , "Found", .Fields("Friday").Value
      End If
      
      .Close
      Debug.Print
    End With
      
    Set rst = Nothing
    Set cnn = Nothing
    
  End Sub


  If I run in the imm. window:

  Call AdoFindDate(#2/1/07#)

  this is returned (my local date format is dd-mm-yyyy):

  #2007/02/01#
                No match
  #1/2/2007#
                No match
  #2/1/2007#
                Found         05-01-2007 

  and with another date:

  Call AdoFindDate(#1/5/07#)
  #2007/01/05#
                Found         05-01-2007 
  #5/1/2007#
                Found         05-01-2007 
  #1/5/2007#
                No match

  This tells me that ONLY the format "yyyy/mm/dd" works which is much different from the general knowledge that the US-format m/d/yyyy would work as well.
  And this means that much code need to be revised if/when transferred to ADO.

  /gustav