Gustav Brock
Gustav at cactus.dk
Sun Jan 28 12:35:28 CST 2007
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