-----Original Message-----
I am using Access 2000. I have a form with a date field. I'd like to be
able to enter a date and have the code find the last entered record before
the date I key in. I'd appreciate any help you could give me. This is
bigger than my feeble ability... Any help would be appreciated. Thanks...
Don Rountree
.
Hi Don, try the following. This assumes that the form is
based on a named object such as a table. If your form uses
an sql string. Save this string as a named query. Also
assumes that recordset has a primary key.
My example sql has a source table1 that includes a primary
key, ID and a date field, aDate. The form has an unbound
text field, txtSearchDate in which a user will enter a
date as a search criteria. The user will then click a
command button to goto a record.
Private Sub Command8_Click()
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strCriteria As String
strSQL = "SELECT Max(Table1.ID) AS MaxOfID " _
& "FROM Table1 " _
& "WHERE (((Table1.aDate)<#" _
& Format(txtSearchDate, "m/d/yy") _
& "#));"
Set rst = CurrentDb.OpenRecordset(strSQL)
With rst
If Not (.BOF And .EOF) Then
strCriteria = "[ID]=" & .Fields
("MaxOfID").Value
Else
MsgBox "No record found..."
End If
.Close
End With
Set rst = Nothing
If Len(strCriteria) > 0 Then
Me.RecordsetClone.FindFirst strCriteria
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub
Luck
Jonathan
