Obtaining Latest Date from Table

I am trying to get the newest date from a table and have it return the record as quickly as possible and feed that date to a textbox.

There are over 200,000 records so I need something that works fast.

My rendition does not work at all. It returns a syntax error of 3075 Syntax Error in Expression.

Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim strDTE As String
Dim recDTE As DAO.Recordset


Set db = CurrentDb()

strDTE = "SELECT Max(sl_date As MxDTE) FROM tblSlip"

Set recDTE = db.OpenRecordset(strDTE, dbOpenDynaset)

With recDTE
    If .EOF = False Then
        .Move First
        Me.txtEnd = !MxDTE
    End If
    .Close
End With

Set recDTE = Nothing

db.Close
Set db = Nothing
End Sub

Open in new window


Any help appreciated.

Thanks

PBLack
PBLackAsked:
Who is Participating?
 
IrogSintaConnect With a Mentor Commented:
Try this:
= DMax("[sl_date]","tblSlip")

No need to use a recordset.
0
 
PBLackAuthor Commented:
I found it here in my bracketing...a brain fart on my part!

strDTE = "SELECT Max(sl_date) As MxDTE FROM tblSlip"
0
 
DOSLoverConnect With a Mentor Commented:
There is a simple syntax error. The 'Max' function should be only on the date.
Please change
             strDTE = "SELECT Max(sl_date As MxDTE) FROM tblSlip"
To
             strDTE = "SELECT Max(sl_date) As MxDTE FROM tblSlip"
And try again.
0
 
IrogSintaConnect With a Mentor Commented:
Here you go:
Private Sub Form_Open(Cancel As Integer)
     Me.txtEnd = DMax("[sl_date]","tblSlip")
End Sub

Open in new window

0
 
PBLackAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.