Trouble Finding Date in Recordset

[GeorgeAtkins]

Access 2003.
I have a form with a simple dropdown, bound to a lookup table. The table
contains a primary key, a date field, and a text field. The dropdown is only
bound to the date and text fields. Limit to List=No, so I can let the user
type in a new date and automatically add it to the lookup table for later
use.

Problem: When the code searches for the date in the recordset, it never
finds it, even if the date is already in the lookup table!

Here is my code. What the heck am I doing wrong? It must be obvious, since
I'm stuck.

Private Sub cboLibDate_AfterUpdate()
' If a new date is entered, update the tblLibraryDinner table
' The combo box is requiried to display the new event date.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim LibPlace As String ' where a new lib dinner held
Dim TheDate As Date
TheDate = Me.cboLibDate.Text
Set db = CurrentDb
Set rs = db.OpenRecordset("tblLibraryDinner", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
rs.FindFirst "[DateOfDinner] = " & TheDate
If rs.NoMatch Then
' the date is not there, so add it.
LibPlace = InputBox("New event! Enter location of dinner", _
"New dinner date: " & Me.cboLibDate)
With rs
.AddNew
!DateOfDinner = Me.cboLibDate
!Location = LibPlace
.Update
End With
Me.cboLibDate.Requery
End If
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

Thanks for any help. - George

 

[Allen Browne]

Access stores the date/time value as a floating point number where the
integer part represents the date, and the fractional part the time of day
(e.g. 0.5 = noon.)

Your code has several possible issues:
1. You use the Afterupdate of the combo. If this combo is bound as you say,
the new date has not been saved in the table yet, so will not be found by
the FindFirst. (Realistically, the combo can only be bound to one field, so
perhaps it is unbound and you are talking about 2 columns in its RowSource?)

2. You declare TheDate to be a variable of type Date, but then you assign it
the Text property of the text box which is a string. This forces some
implicit typecasting. If the date contains a time component as well, this
could have side-effects that are sufficient to trigger rounding issues.
(These problems could occur with the date/time type anyway.)

3. The FindFirst string does not have the # delimiters. Additionally, if the
regional settings are non-US, the entry will be misunderstood. Try:
rs.FindFirst "[DateOfDinner] = " & Format(TheDate, "\#mm\/dd\/yyyy\#")

4. The code searches only for the date. If there were 2 events for the same
date/time, it would not distinguish between them.

5. It might be more efficient to just fetch the matching record:
Dim strSql As String
strSql = "SELECT * FROM tblLibraryDinner WHERE DateOfDinner = " &
Format(TheDate, "\#mm\/dd\/yyyy\#") & ";"
Set rs = db.OpenRecordset(strSql, dbOpenDynaset)
If rs.RecordCount = 0 Then
...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Access 2003.
I have a form with a simple dropdown, bound to a lookup table. The table
contains a primary key, a date field, and a text field. The dropdown is
only
bound to the date and text fields. Limit to List=No, so I can let the user
type in a new date and automatically add it to the lookup table for later
use.

Problem: When the code searches for the date in the recordset, it never
finds it, even if the date is already in the lookup table!

Here is my code. What the heck am I doing wrong? It must be obvious, since
I'm stuck.

Private Sub cboLibDate_AfterUpdate()
' If a new date is entered, update the tblLibraryDinner table
' The combo box is requiried to display the new event date.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim LibPlace As String ' where a new lib dinner held
Dim TheDate As Date
TheDate = Me.cboLibDate.Text
Set db = CurrentDb
Set rs = db.OpenRecordset("tblLibraryDinner", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
rs.FindFirst "[DateOfDinner] = " & TheDate
If rs.NoMatch Then
' the date is not there, so add it.
LibPlace = InputBox("New event! Enter location of dinner", _
"New dinner date: " & Me.cboLibDate)
With rs
.AddNew
!DateOfDinner = Me.cboLibDate
!Location = LibPlace
.Update
End With
Me.cboLibDate.Requery
End If
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

Thanks for any help. - George

 

[GeorgeAtkins]

Allen,
Great response! You are right; sorry about my sloppy terminology: The combo
is, indeed, based on a RowSource. I noticed that, too, after posting, darnit.

The "text" property was a late revision; it has been failing to find the
date no matter what property was listed (or implied). And that was because of
your other good tip: It was those blasted date delimiters that did me in,
after all! This code works just fine:
rs.FindFirst "[DateOfDinner] = #" & TheDate & "#"
If rs.NoMatch Then ...
So I didn't need to waste time with a date variable or CDate function, either.

However, I like your fetch solution the best! Thanks a lot.
George

Access stores the date/time value as a floating point number where the
integer part represents the date, and the fractional part the time of day
(e.g. 0.5 = noon.)

Your code has several possible issues:
1. You use the Afterupdate of the combo. If this combo is bound as you say,
the new date has not been saved in the table yet, so will not be found by
the FindFirst. (Realistically, the combo can only be bound to one field, so
perhaps it is unbound and you are talking about 2 columns in its RowSource?)

2. You declare TheDate to be a variable of type Date, but then you assign it
the Text property of the text box which is a string. This forces some
implicit typecasting. If the date contains a time component as well, this
could have side-effects that are sufficient to trigger rounding issues.
(These problems could occur with the date/time type anyway.)

3. The FindFirst string does not have the # delimiters. Additionally, if the
regional settings are non-US, the entry will be misunderstood. Try:
rs.FindFirst "[DateOfDinner] = " & Format(TheDate, "\#mm\/dd\/yyyy\#")

4. The code searches only for the date. If there were 2 events for the same
date/time, it would not distinguish between them.

5. It might be more efficient to just fetch the matching record:
Dim strSql As String
strSql = "SELECT * FROM tblLibraryDinner WHERE DateOfDinner = " &
Format(TheDate, "\#mm\/dd\/yyyy\#") & ";"
Set rs = db.OpenRecordset(strSql, dbOpenDynaset)
If rs.RecordCount = 0 Then
...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Access 2003.
I have a form with a simple dropdown, bound to a lookup table. The table
contains a primary key, a date field, and a text field. The dropdown is
only
bound to the date and text fields. Limit to List=No, so I can let the user
type in a new date and automatically add it to the lookup table for later
use.

Problem: When the code searches for the date in the recordset, it never
finds it, even if the date is already in the lookup table!

Here is my code. What the heck am I doing wrong? It must be obvious, since
I'm stuck.

Private Sub cboLibDate_AfterUpdate()
' If a new date is entered, update the tblLibraryDinner table
' The combo box is requiried to display the new event date.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim LibPlace As String ' where a new lib dinner held
Dim TheDate As Date
TheDate = Me.cboLibDate.Text
Set db = CurrentDb
Set rs = db.OpenRecordset("tblLibraryDinner", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst
rs.FindFirst "[DateOfDinner] = " & TheDate
If rs.NoMatch Then
' the date is not there, so add it.
LibPlace = InputBox("New event! Enter location of dinner", _
"New dinner date: " & Me.cboLibDate)
With rs
.AddNew
!DateOfDinner = Me.cboLibDate
!Location = LibPlace
.Update
End With
Me.cboLibDate.Requery
End If
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

Thanks for any help. - George