<div>
To do this, you'll need a custom function to count the number of working days between two dates: 
 

<pre><code class="code-10 nolinks" id="code-20-42721678-1">Public Function WorkDays(varStartDt As Variant, varEndDt As Variant) As Variant
'CALCULATES NUMBER OF WORKDAYS (EXCLUDING HOLIDAYS AND WEEKEND DAYS) BETWEEN TWO DATES
'this function requires the existence and maintenance of HolidayTb.HolidayNa and .HolidayDt
'2/9/04 PDG Initial Development
'20091113 PCG rebuilt to return Null if one or two variables are not entered.

If IsNull(varStartDt) Or IsNull(varEndDt) Then
 WorkDays = Null
Else
 Dim DiffCounter As Long, HolidaysCount As Long, CurrDateValue As Date
 DiffCounter = 0
 CurrDateValue = varStartDt
 
'HolidaysCount = DCount(&quot;HolidayNa&quot;, &quot;HolidayTb&quot;, &quot;HolidayDt between #&quot; &amp; varStartDt &amp; &quot;# And #&quot; &amp; varEndDt &amp; &quot;#&quot;)
''loop starts here
 Do While CurrDateValue &lt; varEndDt
 CurrDateValue = CurrDateValue + 1
 If Weekday(CurrDateValue) &gt;= 2 And Weekday(CurrDateValue) &lt;= 6 Then DiffCounter = DiffCounter + 1
 Loop

 WorkDays = (DiffCounter - HolidaysCount)
End If

End Function
</code></pre>
 
This function can also exclude holidays from the count, if you create a HolidayTb with two fields: &nbsp;HolidayNa (text) and HolidayDt (date), and keep it populated. &nbsp;You'll also need to uncomment the HolidaysCount line. 
 
Now that you have a way of counting working days, you can create a recordset (using the criteria you defined), check to see how long since each record's Open_Date, and update the records where its been 10 or more working days. &nbsp;Note that this code assumes that Record_ID is a numeric field. 
 

<pre><code class="code-10 nolinks" id="code-20-42721678-2">Public Sub UpdateAgedRecords()
Dim rs As Recordset, db As Database, strSQL As String

strSQL = &quot;Select Record_ID, Status, Open_Date, Closed_Date from MyData where Status = 'In_Progress' and Closed_Date = ''&quot;
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
 rs.MoveFirst
 Do While rs.EOF = False

'loop through records in rs:
If WorkDays(rs.Fields(&quot;Open_Date&quot;), Date) &gt; 9 Then
 DoCmd.RunSQL &quot;Update MyData set Status = 'Closed', Closed_Date = Now() where Record_ID = &quot; &amp; rs.Fields(&quot;Record_ID&quot;)
End If

rs.MoveNext
Loop
'recordset is closed and emptied:
rs.Close
Set rs = Nothing
End Sub
</code></pre>
</div>

<div>
You just need an update query: 
 

<pre><code class="code-10 nolinks" id="code-20-42722053-1">Update 
 MyData
Set
 Status = 'Closed',
 Closed_Date = Now()
Where
 Status = 'In_Progress'
 And
 Closed_Date Is Null
 And
 VDateDiffWorkdays([Open_Date], Date()) &gt;= 10
</code></pre>
using the functions here: 
 

<pre><code class="code-100 nolinks" id="code-20-42722053-2">Option Explicit

' Common constants.
 
 ' Date.
 ' Workdays per week.
 Public Const WorkDaysPerWeek As Long = 5
 ' Average count of holidays per week maximum.
 Public Const HolidaysPerWeek As Long = 1

' Returns the count of full workdays between Date1 and Date2.
' The date difference can be positive, zero, or negative.
' Optionally, if WorkOnHolidays is True, holidays are regarded as workdays.
'
' Note that if one date is in a weekend and the other is not, the reverse
' count will differ by one, because the first date never is included in the count:
'
' Mo Tu We Th Fr Sa Su Su Sa Fr Th We Tu Mo
' 0 1 2 3 4 4 4 0 0 -1 -2 -3 -4 -5
'
' Su Mo Tu We Th Fr Sa Sa Fr Th We Tu Mo Su
' 0 1 2 3 4 5 5 0 -1 -2 -3 -4 -5 -5
'
' Sa Su Mo Tu We Th Fr Fr Th We Tu Mo Su Sa
' 0 0 1 2 3 4 5 0 -1 -2 -3 -4 -4 -4
'
' Fr Sa Su Mo Tu We Th Th We Tu Mo Su Sa Fr
' 0 0 0 1 2 3 4 0 -1 -2 -3 -3 -3 -4
'
' Execution time for finding working days of three years is about 4 ms.
'
' Requires table Holiday with list of holidays.
'
' 2015-12-19. Gustav Brock. Cactus Data ApS, CPH.
'
Public Function DateDiffWorkdays( _
 ByVal Date1 As Date, _
 ByVal Date2 As Date, _
 Optional ByVal WorkOnHolidays As Boolean) _
 As Long
 
 Dim Holidays() As Date
 
 Dim Diff As Long
 Dim Sign As Long
 Dim NextHoliday As Long
 Dim LastHoliday As Long
 
 Sign = Sgn(DateDiff(&quot;d&quot;, Date1, Date2))
 If Sign &lt;&gt; 0 Then
 If WorkOnHolidays = True Then
 ' Holidays are workdays.
 Else
 ' Retrieve array with holidays between Date1 and Date2.
 Holidays = GetHolidays(Date1, Date2, False) 'CBool(Sign &lt; 0))
 ' Ignore error when using LBound and UBound on an unassigned array.
 On Error Resume Next
 NextHoliday = LBound(Holidays)
 LastHoliday = UBound(Holidays)
 ' If Err.Number &gt; 0 there are no holidays between Date1 and Date2.
 If Err.Number &gt; 0 Then
 WorkOnHolidays = True
 End If
 On Error GoTo 0
 End If
 
 ' Loop to sum up workdays.
 Do Until DateDiff(&quot;d&quot;, Date1, Date2) = 0
 Select Case Weekday(Date1)
 Case vbSaturday, vbSunday
 ' Skip weekend.
 Case Else
 If WorkOnHolidays = False Then
 ' Check for holidays to skip.
 If NextHoliday &lt;= LastHoliday Then
 ' First, check if NextHoliday hasn't been advanced.
 If NextHoliday &lt; LastHoliday Then
 If Sgn(DateDiff(&quot;d&quot;, Date1, Holidays(NextHoliday))) = -Sign Then
 ' Weekend hasn't advanced NextHoliday.
 NextHoliday = NextHoliday + 1
 End If
 End If
 ' Then, check if Date1 has reached a holiday.
 If DateDiff(&quot;d&quot;, Date1, Holidays(NextHoliday)) = 0 Then
 ' This Date1 hits a holiday.
 ' Subtract one day to neutralize the one
 ' being added at the end of the loop.
 Diff = Diff - Sign
 ' Adjust to the next holiday to check.
 NextHoliday = NextHoliday + 1
 End If
 End If
 End If
 Diff = Diff + Sign
 End Select
 ' Advance Date1.
 Date1 = DateAdd(&quot;d&quot;, Sign, Date1)
 Loop
 End If
 
 DateDiffWorkdays = Diff

End Function

' Returns the holidays between Date1 and Date2.
' The holidays are returned as a recordset with the
' dates ordered ascending, optionally descending.
'
' Requires table Holiday with list of holidays.
'
' 2015-12-18. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DatesHoliday( _
 ByVal Date1 As Date, _
 ByVal Date2 As Date, _
 Optional ByVal ReverseOrder As Boolean) _
 As DAO.Recordset
 
 ' The table that holds the holidays.
 Const Table As String = &quot;Holiday&quot;
 ' The field of the table that holds the dates of the holidays.
 Const Field As String = &quot;Date&quot;
 
 Dim rs As DAO.Recordset
 
 Dim SQL As String
 Dim SqlDate1 As String
 Dim SqlDate2 As String
 Dim Order As String
 
 SqlDate1 = Format(Date1, &quot;\#yyyy\/mm\/dd\#&quot;)
 SqlDate2 = Format(Date2, &quot;\#yyyy\/mm\/dd\#&quot;)
 ReverseOrder = ReverseOrder Xor (DateDiff(&quot;d&quot;, Date1, Date2) &lt; 0)
 Order = IIf(ReverseOrder, &quot;Desc&quot;, &quot;Asc&quot;)
 
 SQL = &quot;Select &quot; &amp; Field &amp; &quot; From &quot; &amp; Table &amp; &quot; &quot; &amp; _
 &quot;Where &quot; &amp; Field &amp; &quot; Between &quot; &amp; SqlDate1 &amp; &quot; And &quot; &amp; SqlDate2 &amp; &quot; &quot; &amp; _
 &quot;Order By 1 &quot; &amp; Order
 
 Set rs = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
 
 Set DatesHoliday = rs
 
End Function

' Returns the holidays between Date1 and Date2.
' The holidays are returned as an array with the
' dates ordered ascending, optionally descending.
'
' The array is declared static to speed up
' repeated calls with identical date parameters.
'
' Requires table Holiday with list of holidays.
'
' 2015-12-18. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function GetHolidays( _
 ByVal Date1 As Date, _
 ByVal Date2 As Date, _
 Optional ByVal OrderDesc As Boolean) _
 As Date()
 
 ' Constants for the arrays.
 Const DimRecordCount As Long = 2
 Const DimFieldOne As Long = 0
 
 Static Date1Last As Date
 Static Date2Last As Date
 Static OrderLast As Boolean
 Static DayRows As Variant
 Static Days As Long
 
 Dim rs As DAO.Recordset
 
 ' Cannot be declared Static.
 Dim Holidays() As Date
 
 If DateDiff(&quot;d&quot;, Date1, Date1Last) &lt;&gt; 0 Or _
 DateDiff(&quot;d&quot;, Date2, Date2Last) &lt;&gt; 0 Or _
 OrderDesc &lt;&gt; OrderLast Then
 
 ' Retrieve new range of holidays.
 Set rs = DatesHoliday(Date1, Date2, OrderDesc)
 
 ' Save the current set of date parameters.
 Date1Last = Date1
 Date2Last = Date2
 OrderLast = OrderDesc
 
 Days = rs.RecordCount
 If Days &gt; 0 Then
 ' As repeated calls may happen, do a movefirst.
 rs.MoveFirst
 DayRows = rs.GetRows(Days)
 ' rs is now positioned at the last record.
 End If
 rs.Close
 End If
 
 If Days = 0 Then
 ' Leave Holidays() as an unassigned array.
 Erase Holidays
 Else
 ' Fill array to return.
 ReDim Holidays(Days - 1)
 For Days = LBound(DayRows, DimRecordCount) To UBound(DayRows, DimRecordCount)
 Holidays(Days) = DayRows(DimFieldOne, Days)
 Next
 End If
 
 Set rs = Nothing
 
 GetHolidays = Holidays()
 
End Function


' Returns the count of full workdays between Date1 and Date2.
' The date difference can be positive, zero, or negative.
' Optionally, if WorkOnHolidays is True, holidays are regarded as workdays.
' Returns Null if any parameter is invalid.
'
' Note that if one date is in a weekend and the other is not, the reverse
' count will differ by one, because the first date never is included in the count:
'
' Mo Tu We Th Fr Sa Su Su Sa Fr Th We Tu Mo
' 0 1 2 3 4 4 4 0 0 -1 -2 -3 -4 -5
'
' Su Mo Tu We Th Fr Sa Sa Fr Th We Tu Mo Su
' 0 1 2 3 4 5 5 0 -1 -2 -3 -4 -5 -5
'
' Sa Su Mo Tu We Th Fr Fr Th We Tu Mo Su Sa
' 0 0 1 2 3 4 5 0 -1 -2 -3 -4 -4 -4
'
' Fr Sa Su Mo Tu We Th Th We Tu Mo Su Sa Fr
' 0 0 0 1 2 3 4 0 -1 -2 -3 -3 -3 -4
'
' Execution time for finding working days of three years is about 4 ms.
'
' Requires table Holiday with list of holidays.
'
' 2015-12-19. Gustav Brock. Cactus Data ApS, CPH.
'
Public Function VDateDiffWorkdays( _
 ByVal Date1 As Variant, _
 ByVal Date2 As Variant, _
 Optional ByVal WorkOnHolidays As Boolean) _
 As Variant

 Dim Result As Variant
 
 If IsDate(Date1) And IsDate(Date2) Then
 Result = DateDiffWorkdays(CDate(Date1), CDate(Date2), WorkOnHolidays)
 Else
 Result = Null
 End If
 
 VDateDiffWorkdays = Result

End Function
</code></pre>
and a table of holidays. 
 
Code and table can be found in the attached file. 
<a href="https://filedb.experts-exchange.com/incoming/2018/10_w44/1398470/Work.accdb" target="_blank" class="file-inline" title="Work.accdb (416 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Work.accdb</a>
</div>

Work.accdb

<div>
Paul, On your code...starting from today...11/1/2018 &nbsp; your code is updating records 10/17/2018 and up to today
</div>

<div>
Is there a way to calculate 10 business days prior to todays date and save that &nbsp;variable date and the use that in my sql ? 
 
10 business days prior to today would be ? &nbsp; 10/18/2018 &nbsp;and thats my variable ?
</div>

<div>
gustav, I will try your code later tonight..Thanks for the database.
</div>

<div>
Yes, this function will do that: 
 

<pre><code class="code-10 nolinks" id="code-20-42723795-1">' Adds Number of full workdays to Date1 and returns the found date.
' Number can be positive, zero, or negative.
' Optionally, if WorkOnHolidays is True, holidays are counted as workdays.
'
' Will add 500 workdays in about 0.01 second.
'
' Requires table Holiday with list of holidays.
'
' 2015-12-19. Gustav Brock. Cactus Data ApS, CPH.
'
Public Function DateAddWorkdays( _
 ByVal Number As Long, _
 ByVal Date1 As Date, _
 Optional ByVal WorkOnHolidays As Boolean) _
 As Date
 
 Const Interval As String = &quot;d&quot;
 
 Dim Holidays() As Date

 Dim Days As Long
 Dim DayDiff As Long
 Dim MaxDayDiff As Long
 Dim Sign As Long
 Dim Date2 As Date
 Dim NextDate As Date
 Dim DateLimit As Date
 Dim HolidayId As Long

 Sign = Sgn(Number)
 NextDate = Date1
 
 If Sign &lt;&gt; 0 Then
 If WorkOnHolidays = True Then
 ' Holidays are workdays.
 Else
 ' Retrieve array with holidays between Date1 and Date1 + MaxDayDiff.
 ' Calculate the maximum calendar days per workweek.
 MaxDayDiff = Number * DaysPerWeek / (WorkDaysPerWeek - HolidaysPerWeek)
 ' Add one week to cover cases where a week contains multiple holidays.
 MaxDayDiff = MaxDayDiff + Sgn(MaxDayDiff) * DaysPerWeek
 Date2 = DateAdd(Interval, MaxDayDiff, Date1)
 ' Retrive array with holidays.
 Holidays = GetHolidays(Date1, Date2)
 End If
 Do Until Days = Number
 DayDiff = DayDiff + Sign
 NextDate = DateAdd(Interval, DayDiff, Date1)
 Select Case Weekday(NextDate)
 Case vbSaturday, vbSunday
 ' Skip weekend.
 Case Else
 ' Check for holidays to skip.
 ' Ignore error when using LBound and UBound on an unassigned array.
 On Error Resume Next
 For HolidayId = LBound(Holidays) To UBound(Holidays)
 If Err.Number &gt; 0 Then
 ' No holidays between Date1 and Date2.
 ElseIf DateDiff(Interval, NextDate, Holidays(HolidayId)) = 0 Then
 ' This NextDate hits a holiday.
 ' Subtract one day before adding one after the loop.
 Days = Days - Sign
 Exit For
 End If
 Next
 On Error GoTo 0
 Days = Days + Sign
 End Select
 Loop
 End If
 
 DateAddWorkdays = NextDate

End Function
</code></pre>
</div>

<div>
paul, sorry i meant 10/17/2018 up to today...your code is updating records within my 10 day window and it should not.
</div>

<div>
&gt;On your code...starting from today...11/1/2018 &nbsp; your code is updating records 11/17/2018 and up to today 
I'm sorry, I'm not sure what this means. &nbsp;:) 
 
&gt;Is there a way to calculate 10 business days prior to todays date and save that &nbsp;variable date and the use that in my sql ? 
&gt;10 business days prior to today would be ? &nbsp; 10/18/2018 &nbsp;and thats my variable ? 
 
You'd need another custom function to do that. &nbsp;It could use much of the same logic that the WorkDays function does. &nbsp;Similar to the DateAdd function which comes as part of Access, it would need to accept a Start date and an integer for the number of workdays to be added or subtracted from the Start date. &nbsp;Do you want to take a stab at building it? &nbsp;I think it would be valuable experience for you... &nbsp;:)
</div>

<div>
paul, sorry i meant 10/17/2018 up to today...your code is updating records within my 10 day window and it should not. 
 
but , yes going to step through the code.
</div>

Data Engineer

Fordraiders

<div>
Just use an update query and specifically filter out those days: 
 

<pre><code class="code-1 nolinks" id="code-20-42724101-1">Where VDateDiffWorkdays([Open_Date], Date()) &gt;= 10
</code></pre>
</div>

<div>
Paul Thanks, 
Used &nbsp;debug.print &quot;Working days since OpenDt: &nbsp;&quot; &amp;&nbsp;WorkDays(rs.Fields(&quot;Open_Date&quot;), Date) &nbsp; as my variable. 
 
fordraiders
</div>

<div>
Paul , Gustav, &nbsp;Thank bot of you for very helpful information. 
 
I dont like this new grading system, but i hope both of you get points. ! 
 
 
fordraiders
</div>

<div>
<div class="content wysiwyg-content">
Access 2010 vba 
vba routine needed. 
 
What I have: 
Table - called &quot;MyData&quot; 
Fields- &nbsp;&quot;Record_ID&quot; , &nbsp;&quot;Status&quot; , &quot;Open_Date&quot;, &nbsp;&quot;Closed_Date&quot; 
 
What I need: 
 
I need a routine that will look at the table(&quot;MyData&quot; ) &nbsp;for a (&quot;Status&quot; = &nbsp;&quot;In_Progress&quot;) and (&quot;Closed_Date&quot; = &quot;&quot;) 
Then take the &quot;Open_Date&quot; value and check to see if it is 10 workings or more old. 
 
If it is 
I need to change the &quot;Status&quot; for that record to &nbsp;&quot;Closed&quot; 
AND 
ADD A value to (&quot;Closed_Date&quot; = Now() ) 
 
 
Thanks 
fordraiders
</div>
</div>

Access 2010 vba
vba routine needed.

What I have:
Table - called "MyData" 
Fields-  "Record_ID" ,  "Status" , "Open_Date",  "Closed_Date"

What I need:

I need a routine that will look at the table("MyData" )  for a ("Status" =  "In_Progress") and ("Closed_Date" = "")
Then take the "Open_Date" value and check to see if it is 10 workings or more old.

If it is 
I need to change the "Status" for that record to  "Closed"
AND 
ADD A value to ("Closed_Date" = Now() )


Thanks
fordraiders

Looking at dates and updating records in table

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.