Hi
I have created a simple database to record daily events (events on a
subform on the header record for the day). I have included an unbound
combo control on the main form which is used to move to a particular
date. I am using dd/mm/yyyy format for the dates and the date field is
formatted as short date.

However when a date is entered in the search which could feasibly be
in mm/dd/yyyy format (eg 04/03/2010 for 4th March) it returns the
record for 03/04/2010. In cases where this reversal would not make
sense (eg 23/03/2010) it returns the correct date. What am I doing
wrong? My code associated with the search is as follows:

Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone

rs.FindFirst "[diarydate] = " & "#" & Me.cboMoveTo & "#"
If rs.NoMatch Then
MsgBox "Not Found"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
cboMoveTo = Null
End If
End Sub

On 04/06/2011 12:55:09, Chris wrote:
you might try

Const conDateFormat = "\#dd\/mmm\/yyyy\#"

Not sure whether you need the last "\", but play with it as I usually use
Const conDateFormat = "\#dd\/mmm\/yyyy\ Hh:Nn:Ss#"

rs.FindFirst "DiaryDate = " & Format(Me.cboMove, conDateFormat)

Phil

Chris wrote:

Because the default date style is a windows setting, you
should never rely on it by letting Access auto format a date
that you use in a concatenation expression.

I prefer to do that this way:

rs.FindFirst "diarydate = " & Format(Me.cboMoveTo,
"\#yyyy-m-d\#")

But also check to make sure the date column in the combo box
comes from a Date/Time field, not a Text field. If it's a
Text field, then you may be in trouble. Try using the CDate
function, but if different users have their Windows date
settings to different styles, you'll still have issues for
some users.

--
Marsh