DOB recordset search

[eussias]

The code below is working fine for the majority of the records, but for some reason, every known and then, will not find a specific date that is already in the database. It is used to search for a patient with a specific date of birth. Does anyone know why this may be happening?

Dim rst As Recordset
Dim myBookmark As Date
txtDate.SetFocus
If txtDate.Text = "" Then
MsgBox "You must enter a Date of Birth"
Exit Sub
End If
Set rst = CurrentDb.OpenRecordset("SELECT * FROM ClientInfo WHERE [DOB] = #" & txtDate.Text & "#&quot
On Error Resume Next
Dim MyValue As Date
MyValue = txtDate
rst.MoveFirst
If rst.Fields(&quot;DOB&quot <> MyValue Then
MsgBox &quot;No record for that patient&quot;, vbInformation, &quot;LMTC Database&quot;
txtDate.Text = &quot; &quot;
txtDate.SetFocus
Exit Sub
End If
Me.RecordsetClone.FindFirst &quot;[DOB] = #&quot; & MyValue & &quot;#&quot;
Me.Bookmark = Me.RecordsetClone.Bookmark

 

[rewdee]

Perhaps, you are getting a result but your DOB field might have time in it or there's a difference in formats between the textbox and the DOB field. Hence the values are not exact but the messagebox appears. Have you tried using something like:

Debug.print "DOB Field Value: " & rst.Fields("DOB") _
            & vbcrlf & "MyValue": " & cstr(MyValue)

Hope this helps,
Rewdee