473,324 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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 16315
-----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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: rivka.howley | last post by:
I wrote some code that creates a table with a date/time field at 15-minute intervals. Here's how I create and populate the table With tblDataTemp ..Fields.Append .CreateField("CT_ID", dbLong)...
0
by: keri | last post by:
I have a calendar that currently shows the name of a customer in the correct date. However it does not show multiple appointments on the same day. I have worked out why it does this, but i am not...
2
by: keri | last post by:
Hi everyone, I'm using findfirst in code as below. It searches a query that shows appointment date and account name and should return the account name of the first appointment on each day into...
1
Corster
by: Corster | last post by:
I went through a great deal of hassle to figure this out for myself, but now it is complete, I would like to share it with the world! I know afew other people have had trouble with FindFirst and...
2
by: JasCot75 | last post by:
Hello all, my first go at this so please be gentle. I'm putting together a simple access database and have run into a problem with date function I found on the web. The code appears to work, but...
7
by: waltvw | last post by:
I'm using FindFirst method in Access VBA to find a particular record in a recordset. I have 2 search criteria each of which works just fine if used separately as an argument in FindFirst, but NOT in...
3
by: boliches | last post by:
I am trying to get a continuous form to highlight lines individually (ideally on hover). I have used the following code as recommended by a previous entry on this website. My problems begin in that...
2
by: Denise | last post by:
Front end is Access 2002, back end is linked Oracle tables. My users need to describe things in feet and inches and want to use the standard ' and " abbrevations. On a testing form I go to a...
25
by: Rick Collard | last post by:
Using DAO 3.6 on an Access 2002 database, I'm getting unexpected results with the FindFirst method. Here's the simple code to test: Public Sub FindIt() Dim db As Database, rs As Recordset...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.