Passing field names to a Function or Sub

[Kenny1943]

I have a table that contains dates of completion for a process. There are five different dates for each record. Each date represents the completeion of a step in the process. I am trying to calculate the workings days between each of these 5 dates. I use a function to calc the working days.

This is the code I wrote to update the working days once it is calculated. CountWorkdays is a function used to calculate the days.

How can I pass the field names to the Sub so I can call this and use it with each of the updates?

Sub CalcDays(sTable As String, sUpdateField As String, sDatefield As String, eDateField As String)

Dim db As dao.Database, rst As dao.Recordset
Set db = CurrentDb

Set rst = db.OpenRecordset(sTable, dbOpenDynaset)

Do Until rst.EOF
With rst
.Edit
!sUpdateField = (CountWorkdays(!sDatefield, !eDateField))
.Update
.MoveNext
End With
Loop

rst.Close
db.Close
End Sub

 

[BiggerBrother]

function CountWorkDays(Date1 as date, date2 as date) as long
CountWorkDays=datediff("d",date1,date2)
end function

This function will return the days between the two dates, not just the weekdays.

BB

 

[Kenny1943]

BigBrother

I have a function to calc the works days. But what I need is a generic sub to update the work days once calculated. How can I pass the field names to the Sub?

 

[MarkSweetland]

Use the Fields collection to find the column(s) you want to update using the strings passed as parameters:


With rst
.Edit
.Fields(sUpdateField) = (countWorkdays(.Fields(sDatefield), .Fields(eDateField)))
.Update
.MoveNext
End With



Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[Kenny1943]

MarkSweetland

This is works great. Thanks for your help.