jbakestull
asked on
Access 2007 Updated table (loop)
I have a table called tblChronicallyHomelessComb
Client
Entry Date
Exit Date
Length
I need VBA code that would loop through the table and update Length as ([entry date]-[exit date]).
I tried looking for an example but not having much luck.
Client
Entry Date
Exit Date
Length
I need VBA code that would loop through the table and update Length as ([entry date]-[exit date]).
I tried looking for an example but not having much luck.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
correction in the codes
With rst
.MoveFirst
Do Until .EOF
' .MoveFirst remove from this location
.Edit
!Length = DateDiff("d", ![Exit Date],![Entry Date])
.Update
.movenext ' add this line
Loop
End With
With rst
.MoveFirst
Do Until .EOF
' .MoveFirst remove from this location
.Edit
!Length = DateDiff("d", ![Exit Date],![Entry Date])
.Update
.movenext ' add this line
Loop
End With
ASKER
Public Function Test()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb()
strSQL = "SELECT * FROM tblChronicallyHomelessComb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
With rst
Do Until rst.EOF
.MoveFirst
.Edit
!Length = Date
.Update
Loop
End With
End Function
But the code keeps running.