[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 576
  • Last Modified:

Access 2007 Updated table (loop)

I have a table called tblChronicallyHomelessCombine with the following fields.
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.
0
jbakestull
Asked:
jbakestull
  • 2
1 Solution
 
jbakestullAuthor Commented:
I've tried

Public Function Test()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

 
Set db = CurrentDb()
strSQL = "SELECT * FROM tblChronicallyHomelessCombine"
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.
0
 
Rey Obrero (Capricorn1)Commented:
although, you don't really need this codes to update the field [Lentgh}, since you can always derive the value using a query, any way


Public Function Test()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

 
Set db = CurrentDb()
strSQL = "SELECT * FROM tblChronicallyHomelessCombine"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
With rst
   Do Until .EOF
      .MoveFirst
      .Edit
      !Length = DateDiff("d", ![Exit Date],![Entry Date])

      .Update
   
     .movenext  ' add this line
Loop
End With
 End Function


the same function you can use in a query

select Client,[Entry Date],[Exit Date], DateDiff("d",[Exit Date],[Entry Date]) as LengthOfStay
from tblChronicallyHomelessCombine

to use an update query

Update tblChronicallyHomelessCombine
set [Length]= DateDiff("d",[Exit Date],[Entry Date])
0
 
Rey Obrero (Capricorn1)Commented:
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
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now