Max(DATE) and Max(Time)

[Executable]

Hi all,

I have a table with several records that contain a SerialNumber, Date and Time. The same serial number can have several times and dates. What I'm trying to do is generate a SQL statement in VB to get the latest date and time when that serial number was updated.

I tried using:

 "SELECT SN,Date,Time, FROM Table1, (SELECT MAX(Date),MAX(Time) FROM Table1) WHERE SN=some value"

But it does not work.

Thanks,

[NeoPa]

I would say the complication here is the max time value. This is related to storing the two values separately, which is never recommended (I expect you can see part of the reason why now ;) ). Try this as a solution for now, but I recommend you change your design so you don't need to jump through hoops to handle your data.

SELECT tT.SN
     , tT.Date
     , tT.Time
FROM   [Table1] AS tT
       INNER JOIN
    (
    SELECT   SN
           , Max([Date]) AS MaxDate
           , TimeValue(Max([Date] + [Time])) AS MaxTime
    FROM     [Table1]
    GROUP BY [SN]
    ) AS subMax
   ON  tT.SN = subMax.SN
  AND  tT.Date = subMax.MaxDate
  AND  tT.Time = subMax.MaxTime

Welcome to Bytes!

[Executable]

Hi NeoPa,

Thanks for the lesson I really appreciate it. If I wanted to put this on a string would this work?

SQL = "SELECT tT.[SN],tT.[Time] FROM [Table1] INNER JOIN (SELECT SN,Max([Date]),TimeValue(Max([Date] + [Time])) FROM [Table1])"

[NeoPa]

Executable:
Hi NeoPa,

I'm getting the following error

Run-Time error '3131'
Syntax error in FROM clause

When I try the following:

strSQL = "SELECT tT.[Date],tT.[Time] FROM [Table1] INNER JOIN (SELECT SN,Max([Date]),TimeValue(Max([Date] + [Time])) FROM [Table1])"

This is from your code:

SELECT tT.SN
     , tT.Date
     , tT.Time
FROM   [Table1] AS tT
       INNER JOIN
    (
    SELECT   SN
           , Max([Date]) AS MaxDate
           , TimeValue(Max([Date] + [Time])) AS MaxTime
    FROM     [Table1]
    GROUP BY [SN]
    ) AS subMax
   ON  tT.SN = subMax.SN
  AND  tT.Date = subMax.MaxDate
  AND  tT.Time = subMax.MaxTime

Any Idea what's going on?

Thanks

I received this via PM (which is a big no-no!)

I'm happy to proceed with this thread but never PM technical questions to anybody.

[NeoPa]

Check out this snippet (included below). If I get some time later I'll have another look at it, but it's much easier to work with the resultant string than the original VBA (which I have no access to until you post it of course).

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

If the problem is still unclear then take the resultant SQL string (copied to clipboard in previous paragraph) and paste it into the SQL view of any query. From here you can try to switch to Design View. If that works, then you may try to run it (or simply switch to Datasheet View which shows data but doesn't apply any changes - for action queries).