469,358 Members | 1,626 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,358 developers. It's quick & easy.

FindFirst with a Date Field

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

Nov 13 '05 #1
4 15154
-----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.

Nov 13 '05 #2
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

Nov 13 '05 #3
> .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

Nov 13 '05 #4
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-----
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by keri | last post: by
2 posts views Thread by keri | last post: by
25 posts views Thread by Rick Collard | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.