Pimary Key as date type, bug finding record

[Pedro Pinto]

I'm dealling with a problem on positioning a form record by seeking a date primary field.

I have simple table with two data fields:
1st field- date as short-date (table primary field)
2nd field - description as text

Every time i use the method me.recordsetclone.findfirst to match a record by date, if the search date starts with a zero (i.e 02-09-2006) it gives me nomatch but the record exists! If the date doesn't start with zero (i.e 20-09-2006) it matches the record.

How is this possible? Am i doing something wrong? Is there a way to resolve this?

I retreive the search date by clicking on a listbox with the dates records of the table "MyDates"

The code i use is this:

Private Sub List0_Click()

With Me
.RecordsetClone.FindFirst "[MyDates] = #" & me.list0 & "#"
If Not .RecordsetClone.NoMatch Then
.Bookmark = .RecordsetClone.Bookmark
.Recordset.Bookmark = .RecordsetClone.Bookmark
Else
MsgBox ("Record not found")
End If
End With

End Sub

Thank you.

Note: i've tried "dlookup" method and it gives me the same results.

 

[RuralGuy]

International Dates in Access
Return Dates in US #mm/dd/yyyy# format

 

[Pedro Pinto]

International Dates in Access
Return Dates in US #mm/dd/yyyy# format

I've made the necessary corrections and now it's working.

Problem: In VBA code dates must be delimited with the "#" symbol, but regardless the computer regional settings, Access expects these literal dates to be in the American format, e.g. #12/31/1999# (mm/dd/yyyy). My dates has the format (dd-mm-yyyy).

Solution: I forced the date format with Format(mydate, "mm\-dd\-yyyy")

The new working code is like this:

Private Sub List0_Click()

With Me
.RecordsetClone.FindFirst "[MyDates] = #" & _
Format(Me.List0, "mm\-dd\-yyyy")& "#"
If Not .RecordsetClone.NoMatch Then
.Bookmark = .RecordsetClone.Bookmark
.Recordset.Bookmark = .RecordsetClone.Bookmark
Else
MsgBox ("Record not found")
End If
End With

End Sub

Thanks

 

[Rich]

Just stick to ONE post on the topic in future!
http://www.access-programmers.co.uk/forums/showthread.php?t=114791

 

[RuralGuy]

Glad you got it working Pedro. Thanks for posting back.