challenging update query

[atadbitslow]

I have a table that has logdate, lognumber, batchid. I need to create a query or code that will go through the table and make the logdates the same per lognumber. So if lognumber 4321, has 5 records, but 3 different logdates, I need to update the logdates to be the same for lognumber 4321, preferrably using the latest date.

Thanks!

 

[Terpsfan]

The key here is to use the domain function Dlast to get your last date entered for that log number. In the Dlast function the first parameter is the field, second the table, third is a where statement. I'm not sure what the name of your table is, so you would just replace tablename with your actual table name. And lastly I'm assumed lognumber is a numeric data type. If it's text you'll have to use '4321' instead.


Update tablename
Set lognumber = 4321, logdate = DLast("logdate","tablename","[lognumber] = 4321&quot
WHERE lognumber = 4321;

 

[atadbitslow]

well 4321 was just an example of a log number, I need to be able to go through the entire table (cash items)......

 

[Terpsfan]

Ok, try this, create a new form and add a text box called txtLogNumber and a command button called cmdUpdate. Then try this code.

Dim Database As DAO.Database
Dim objRec As DAO.Recordset

Private Sub cmdUpdate_Click()
Set Database = CurrentDb
Set objRec=Database.OpenRecordset"tblCashItems&quot

Do Until objRec.EOF
If objRec("lognumber&quot = txtLogNumber Then
objRec.Edit
objRec("logdate&quot = DLast("logdate", "tblCashItems", "[lognumber] = '" & txtLogNumber & "'&quot
objRec.Update
End If
objRec.MoveNext
Loop
objRec.Close
Database.Close
Set objRec = Nothing
Set Database = Nothing

End Sub

 

[Terpsfan]

Or if you are dealing with a very large table, this code will work faster:

Dim Database As DAO.Database
Dim objRec As DAO.Recordset

Private Sub cmdUpdate_Click()
Dim SQL As String
Set Database = CurrentDb
Set objRec = Database.OpenRecordset("tblCashItems", dbOpenTable)
Dim Last As String

Last = DLast("logdate", "tblCashItems", "[lognumber] = '" & txtLogNumber & "'&quot

SQL = "Update tblCashItems Set logdate = '" & Last & "'"
SQL = SQL & " WHERE lognumber = '" & txtLogNumber & "'"

Database.Execute SQL, dbFailOnError

objRec.Close
Database.Close
Set objRec = Nothing
Set Database = Nothing

End Sub

 

[simmsey]

I think you can use something similar to:

UPDATE Table1
SET Table1.[logdate] = DMax("logdate","table1","[lognumber]='" & [lognumber] & "'"

That should run through the records and set the logdate to the max value of logdate found for that particular lognumber.

 

[Terpsfan]

You're right I must have had something wrong in my brain. You have to use the Dmax function. Plus yours works with a simple SQL statement, rather than 20 lines of code. You could use the DMAX in my examples and I think get the same result, but I like yours better.