Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Obtaining Latest Date from Table

Posted on 2013-05-25
5
Medium Priority
?
437 Views
Last Modified: 2013-05-25
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
0
Comment
Question by:PBLack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 

Author Comment

by:PBLack
ID: 39196631
I found it here in my bracketing...a brain fart on my part!

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

Accepted Solution

by:
IrogSinta earned 1600 total points
ID: 39196634
Try this:
= DMax("[sl_date]","tblSlip")

No need to use a recordset.
0
 
LVL 5

Assisted Solution

by:DOSLover
DOSLover earned 400 total points
ID: 39196663
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
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 1600 total points
ID: 39196672
Here you go:
Private Sub Form_Open(Cancel As Integer)
     Me.txtEnd = DMax("[sl_date]","tblSlip")
End Sub

Open in new window

0
 

Author Closing Comment

by:PBLack
ID: 39196687
Thanks
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question