469,270 Members | 1,153 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

Max(DATE) and Max(Time)

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:

Expand|Select|Wrap|Line Numbers
  1.  "SELECT SN,Date,Time, FROM Table1, (SELECT MAX(Date),MAX(Time) FROM Table1) WHERE SN=some value"
  2.  
But it does not work.

Thanks,
Sep 8 '10 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. SELECT tT.SN
  2.      , tT.Date
  3.      , tT.Time
  4. FROM   [Table1] AS tT
  5.        INNER JOIN
  6.     (
  7.     SELECT   SN
  8.            , Max([Date]) AS MaxDate
  9.            , TimeValue(Max([Date] + [Time])) AS MaxTime
  10.     FROM     [Table1]
  11.     GROUP BY [SN]
  12.     ) AS subMax
  13.    ON  tT.SN = subMax.SN
  14.   AND  tT.Date = subMax.MaxDate
  15.   AND  tT.Time = subMax.MaxTime
Welcome to Bytes!

4 8913
NeoPa
32,171 Expert Mod 16PB
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT tT.SN
  2.      , tT.Date
  3.      , tT.Time
  4. FROM   [Table1] AS tT
  5.        INNER JOIN
  6.     (
  7.     SELECT   SN
  8.            , Max([Date]) AS MaxDate
  9.            , TimeValue(Max([Date] + [Time])) AS MaxTime
  10.     FROM     [Table1]
  11.     GROUP BY [SN]
  12.     ) AS subMax
  13.    ON  tT.SN = subMax.SN
  14.   AND  tT.Date = subMax.MaxDate
  15.   AND  tT.Time = subMax.MaxTime
Welcome to Bytes!
Sep 9 '10 #2
Hi NeoPa,

Thanks for the lesson I really appreciate it. If I wanted to put this on a string would this work?
Expand|Select|Wrap|Line Numbers
  1. SQL = "SELECT tT.[SN],tT.[Time] FROM [Table1] INNER JOIN (SELECT SN,Max([Date]),TimeValue(Max([Date] + [Time])) FROM [Table1])"
Sep 9 '10 #3
NeoPa
32,171 Expert Mod 16PB
Executable:
Hi NeoPa,

I'm getting the following error

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


When I try the following:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. SELECT tT.SN
  2.      , tT.Date
  3.      , tT.Time
  4. FROM   [Table1] AS tT
  5.        INNER JOIN
  6.     (
  7.     SELECT   SN
  8.            , Max([Date]) AS MaxDate
  9.            , TimeValue(Max([Date] + [Time])) AS MaxTime
  10.     FROM     [Table1]
  11.     GROUP BY [SN]
  12.     ) AS subMax
  13.    ON  tT.SN = subMax.SN
  14.   AND  tT.Date = subMax.MaxDate
  15.   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.
Sep 10 '10 #4
NeoPa
32,171 Expert Mod 16PB
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).
Sep 10 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by HandersonVA | last post: by
7 posts views Thread by Hieronimus | last post: by
1 post views Thread by Matik | last post: by
1 post views Thread by jas2803 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.