VBA for Due Date Count and Day Count

[iuianj07]

Hello Guys,

I have a public function that counts turnaround date/days. The code is:

Public Function getDueDate(ByVal recCnt As Variant, imageToVendor As Variant) As Date

Dim turnAroundDays As Integer
Dim intCount As Integer
If IsNumeric(recCnt) And IsDate(imageToVendor) Then
    turnAroundDays = getTurnAroundDays(recCnt)
    getDueDate = imageToVendor
    Do
        getDueDate = getDueDate + 1
        If Not (Weekday(getDueDate) = vbSaturday Or Weekday(getDueDate) = vbSunday) Then
        intCount = intCount + 1
        End If
    Loop Until intCount = turnAroundDays
End If

End Function

Then in my query:

SELECT Wells_Tracking_Log_Staging_Area.StagingID, Wells_Tracking_Log_Staging_Area.FS_ImageToVendor, (SELECT Count(StagingID) FROM Wells_Tracking_Log_Staging_Area as A WHERE Wells_Tracking_Log_Staging_Area.FS_ImageToVendor = A.FS_ImageToVendor) AS jobCount, getDueDate([jobCount],[FS_ImageToVendor]) AS DueDate, [DueDate]-[FS_ImageToVendor] AS Days
FROM Wells_Tracking_Log_Staging_Area;

I have a column named "Days", what I was hoping to have was that it would give me the number of days between the DueDate and FS_ImageToVendor Date excluding Weekends. In my example:

I have 1014 records with 8/3/2010 as FS_ImageToVendor Date, so with the function and query I have above, the DueDate will be 8/17/2010 (14 days difference because of 2 weekends between the 2 dates) but the total Turnaround Days is only 10 days right?

On my column Days field, I was hoping I could get 10 instead of 14 days. I know that base on my query above, DueDate - FS_ImageToVendor is wrong.

Hopefully you could help me with this.

Thank you

 

[PHV]

I'd replace this:
, [DueDate]-[FS_ImageToVendor] AS Days
with this:
, getTurnAroundDays([jobCount]) AS Days

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iuianj07]

Thank you PHV