Selecting the Most current date from VB6 1

[ChiliDog007]

i have created a front end in VB6 to an SQL database. i then use that front end to populate a table. the problem is that i can not select only the most current date. i have tried many different approaches but i can't figure out how to do this. i have a training session that i have to give on Oct 21 and i need to know very soon. here is my sql statement. it will select all dates, i need to select only the most current date.

Report = "SELECT " _
       & "[Machine Data].[Machine Number], " _
       & " [Machine Data].[Machine Description], " _
       & " [Machine Data].Dept, " _
       & " [Machine Data].Disposition, " _
       & " [Machine Data].SafeRequire, " _
       & " SafeGuard.IDate, " _
       & " SafeGuard.Passed " _
       & "From " _
       & "[Machine Data] " _
       & "LEFT OUTER JOIN " _
       & "SafeGuard " _
       & " ON [Machine Data].[Machine Number] = SafeGuard.MachNum " _
       & " WHERE [Machine Data].SafeRequire = 1 " _
       & " Group By " _
       & " [Machine Data].[Machine Number], " _
       & " [Machine Data].[Machine Description], " _
       & " [Machine Data].Dept, " _
       & " [Machine Data].Disposition, " _
       & " [Machine Data].SafeRequire, " _
       & " SafeGuard.IDate, " _
       & " SafeGuard.Passed " _
       & " Order By " _
       & " [Machine Data].[Machine Number], " _
       & " MAX (SafeGuard.IDate) "

 

[rdgerken]

SELECT TOP 1 ....


Will that work?

 

[DrJavaJoe]

Use a subquery

Select * from SomeTable where SomeDate = (select top 1 SomeDate from SomeTable order by SomeDate desc)

Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[ChiliDog007]

So that would go in my SQL Statement like this?
= "SELECT " _
& "[Machine Data].[Machine Number], " _
& " [Machine Data].[Machine Description], " _
& " [Machine Data].Dept, " _
& " [Machine Data].Disposition, " _
& " [Machine Data].SafeRequire, " _
& " SafeGuard.IDate, " _
& " SafeGuard.Passed " _
& "From " _
& "[Machine Data] " _
& "LEFT OUTER JOIN " _
& "SafeGuard " _
& " ON [Machine Data].[Machine Number] = SafeGuard.MachNum " _
& " WHERE [Machine Data].SafeRequire = 1 " _
& " AND SafeGuard.IDate=(SELECT TOP 1 SafeGuard.IDate FROM SafeGuard Order By IDate Desc) "_
& " Group By " _
& " [Machine Data].[Machine Number], " _
& " [Machine Data].[Machine Description], " _
& " [Machine Data].Dept, " _
& " [Machine Data].Disposition, " _
& " [Machine Data].SafeRequire, " _
& " SafeGuard.IDate, " _
& " SafeGuard.Passed " _
& " Order By " _
& " [Machine Data].[Machine Number], " _
& " SafeGuard.IDate

 

[ChiliDog007]

i tried that and it work except that it only gives one record. there are 124 machines and i just need the most current date. any more ideas?

 

[DrJavaJoe]

The subquery should use the same From clause, try this:

"SELECT " _
& "[Machine Data].[Machine Number], " _
& " [Machine Data].[Machine Description], " _
& " [Machine Data].Dept, " _
& " [Machine Data].Disposition, " _
& " [Machine Data].SafeRequire, " _
& " SafeGuard.IDate, " _
& " SafeGuard.Passed " _
& "From " _
& "[Machine Data] " _
& "LEFT OUTER JOIN " _
& "SafeGuard " _
& " ON [Machine Data].[Machine Number] = SafeGuard.MachNum " _
& " WHERE [Machine Data].SafeRequire = 1 " _
& " AND SafeGuard.IDate=(SELECT TOP 1 SafeGuard.IDate From " _
& "[Machine Data] " _
& "LEFT OUTER JOIN " _
& "SafeGuard " _
& " ON [Machine Data].[Machine Number] = SafeGuard.MachNum ") "
& " Group By " _
& " [Machine Data].[Machine Number], " _
& " [Machine Data].[Machine Description], " _
& " [Machine Data].Dept, " _
& " [Machine Data].Disposition, " _
& " [Machine Data].SafeRequire, " _
& " SafeGuard.IDate, " _
& " SafeGuard.Passed " _
& " Order By " _
& " [Machine Data].[Machine Number], " _
& " SafeGuard.IDate "

Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[ChiliDog007]

Thnax for the help i was able to get it to work.

If a tree falls in the woods, and no one is there to see it, do all the other trees make fun of it?