[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