FindFirst with a Date Field

[jon.lewis]

I have a table in Access 97 with [BillDate] a Date/Time type field
Formated "Short Date", UK local settings (dd/mm/yy).

Want to go to a record with a date selected from a combo (correctly
Short Date formatted) on a form.

Me.RecordsetClone.FindFirst "[BillDate] = #" & Me![Combo2] & "#"
If RecordsetClone.NoMatch Then
MsgBox "No Match"
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

Code works fine for dates up to 31/12/00 but produces No Match for
dates > 01/01/01. I'm aware of most of the issues re ambiguous date
formats but can't find anything on the Web for this specific issue.

All help much appreciated.

TIA

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You may wish to try converting the ComboBox's value to a date:

' It's better to have an obj ref variable, rather than the complete obj.
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs
.FindFirst "[BillDate] = " & CDate(Me![Combo2])
If .NoMatch Then
MsgBox "No Match"
Else
Me.Bookmark = .Bookmark
End If
End With

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkoHVYechKqOuFEgEQLN1ACg2DLBdaDZMTgoGJWsf0X7GJ zh2MsAoIHH
gS3bNHVyLjyPMHJoe6/mTCWC
=YzG8
-----END PGP SIGNATURE-----
jo*******@btinternet.com wrote:

I have a table in Access 97 with [BillDate] a Date/Time type field
Formated "Short Date", UK local settings (dd/mm/yy).

Want to go to a record with a date selected from a combo (correctly
Short Date formatted) on a form.

Me.RecordsetClone.FindFirst "[BillDate] = #" & Me![Combo2] & "#"
If RecordsetClone.NoMatch Then
MsgBox "No Match"
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

Code works fine for dates up to 31/12/00 but produces No Match for
dates > 01/01/01. I'm aware of most of the issues re ambiguous date
formats but can't find anything on the Web for this specific issue.

[Rog]

Jon,

Access gets confused with non-American date settings, you can either
convert the dates as MGFoster suggests, or use Dev Ashish's solution:
http://www.mvps.org/access/datetime/date0005.htm

[jon.lewis]

> .FindFirst "[BillDate] = " & CDate(Me![Combo2])

Thanks but the above gives a "Division by zero" error.

..FindFirst "[BillDate] = #" & Format(Me![Combo2], "mm\/dd\/yy") & "#"

is the only way I could get it working. I was interested in the pre
and after Y2K issue and if this was a known bug.

Jon

[MGFoster]

jo*******@btinternet.com wrote:

.FindFirst "[BillDate] = " & CDate(Me![Combo2])

Thanks but the above gives a "Division by zero" error.

.FindFirst "[BillDate] = #" & Format(Me![Combo2], "mm\/dd\/yy") & "#"

is the only way I could get it working. I was interested in the pre
and after Y2K issue and if this was a known bug.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I couldn't replicate the error in the debug window. CDate() worked on
these variations:

1/1/00 -> 1/1/2000
1/1/0 -> 1/1/2000
01/01/01 -> 1/1/2001

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkr+BYechKqOuFEgEQKF2wCg/TCsabdFsrSQDbIsNpGt19VGPOUAn29C
IxW7nUTUi/jKd6QF2UqY7pkg
=Mh7C
-----END PGP SIGNATURE-----