Latest Date Query 1

[sanders720]

I would like at access a table and get the data with the latest date in the table. That date could be any date or time, so I am unable to write code for specifics.

 

[Databaseguy]

Use the Max function Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[sanders720]

Can I get an example VB6 command specific?

 

[Databaseguy]

Set reference to the DAO 3.6 model in your application.

Private Sub GetLastDate()
Dim DBS as database
Dim rst as recordset
Dim strSQL as string
Dim MyDateVar as date
Set dbs = "File and pathname of DB"
strSQL = "SELECT Max(YourTable.YourField) AS MaxDate FROM YourTable"
Set rst = dbs.openrecordset(strSQL)
MyDateVar = rst.fields(x).value 'where X is the filed the date is in
me.txtLastDate = MyDateVar
Set rst = nothing
Set dbs = nothing
End Sub

Now that I'm typing, you could probably use the DMAx function also....
Tyrone Lumley
augerinn@gte.net

http://server105.hypermart.net/databaseguy

 

[sanders720]

Private Sub GetHistory()
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim sqlX As String

With cmd
.ActiveConnection = conn

' *** UPON THIS SQL, MANY RECORDS WILL APPEAR FOR THE SAME THING WITH DIFFERENT DATES.
' I NEED TO WRITE THIS QUERY SO THAT THE LATEST DATE IS THE ONE USED ***

.CommandText = "SELECT QTY FROM tblBOM_History" & _
"WHERE JobNo = " & SubAssy.JobNo & " " & _
"AND SubAssy = " & SubAssy.SubAssy

.Execute




End With
End Sub

 

[sanders720]

STILL LOOKING FOR AN ANSWER...UPON THIS SQL, MANY RECORDS WILL APPEAR FOR THE SAME THING WITH DIFFERENT DATES. I NEED TO WRITE THIS QUERY SO THAT THE LATEST DATE IS THE ONE USED

Private Sub GetHistory()
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim sqlX As String

With cmd
.ActiveConnection = conn

' *** UPON THIS SQL, MANY RECORDS WILL APPEAR FOR THE SAME THING WITH DIFFERENT DATES.
' I NEED TO WRITE THIS QUERY SO THAT THE LATEST DATE IS THE ONE USED ***

.CommandText = "SELECT QTY FROM tblBOM_History" & _
"WHERE JobNo = " & SubAssy.JobNo & " " & _
"AND SubAssy = " & SubAssy.SubAssy

.Execute




End With
End Sub

 

[cmmrfrds]

If you only need 1 record you could try the TOP keyword.

SELECT TOP 1 etc........

ORDER BY date desc

 

[sanders720]

What is the syntax for this?

 

[cmmrfrds]

CommandText = "SELECT TOP 1 QTY FROM tblBOM_History" & _
"WHERE JobNo = " & SubAssy.JobNo & " " & _
"AND SubAssy = " & SubAssy.SubAssy & " " & _
"ORDER BY tblBOM_History desc "

 

[cmmrfrds]

Sorry, I put the table name in where your date should be.

CommandText = "SELECT TOP 1 QTY FROM tblBOM_History" & _
"WHERE JobNo = " & SubAssy.JobNo & " " & _
"AND SubAssy = " & SubAssy.SubAssy & " " & _
"ORDER BY YourLatestDate desc "

 

[BoxHead]

Sanders720

I'm running Access '97 so I don't know if this willwork for you, but try it.

Go to the database window, select the Queries tab and click "New". Click "OK" for Design view.

The query design grid will open along with the Show Table window. Select the tables/queries to be included and close the Show Table window.

Double click the fields you want in your query to place them in the grid. If you don't have a row in the grid called "Totals", then right-click on one of the grid's cells and select 'Totals' from the menu. This will add the row.

In the date column, click the drop-down in the Totals row and select MAX.

Select View: Datasheet View from the main menu to view the results.

Did that give you what you want?

If it did, switch to SQL view and copy the SQL. Go back to your module and paste it in.

Also, if it does work, please paste and post the SQL here for us to see.

If it doesn't work, I guess it's another ten minutes without an answer and I apologize.


HTH John

Use what you have,
Learn what you can,
Create what you need.