Calculating Turnaround Time error message

[iuianj07]

Hello guys,

I am trying to calculate number of turnaround days between two dates, we basically have a job tracking database, where we receive a bunch of loans/deals from the client, we enter the loan/deal information into the database and track the status of each database depending on how we work on it...

I have a query below:

SELECT tblJobTracking.SitusID, (SELECT StatusDate FROM tblDealStatus WHERE StatusChangeID = 1 AND tblDealStatus.SitusID = tblJobTracking.SitusID) AS DateReceived, (SELECT StatusDate FROM tblDealStatus WHERE StatusChangeID = 8 AND tblDealStatus.SitusID = tblJobTracking.SitusID) AS DateSent, IIf([tblJobTracking].[ConsolidatedFS]=-1 And [tblJobTracking].[ConsolidatedRR]=-1,1,[PropertyCount]) AS [Property Count], IIf((DeltaDays((SELECT StatusDate FROM tblDealStatus WHERE StatusChangeID = 1 AND tblDealStatus.SitusID = tblJobTracking.SitusID),(SELECT StatusDate FROM tblDealStatus WHERE StatusChangeID = 8 AND tblDealStatus.SitusID = tblJobTracking.SitusID))-1)=0,"Same Day",DeltaDays((SELECT StatusDate FROM tblDealStatus WHERE StatusChangeID = 1 AND tblDealStatus.SitusID = tblJobTracking.SitusID),(SELECT StatusDate FROM tblDealStatus WHERE StatusChangeID = 8 AND tblDealStatus.SitusID = tblJobTracking.SitusID))-1) AS WkDays, tblJobTracking.WeekNumber
FROM tblJobTracking
WHERE (((tblJobTracking.SitusID) Not In (Select Distinct tblDealStatus.SitusID FROM tblDealStatus WHERE StatusChangeID = 9 OR StatusChangeID = 10)) AND ((tblJobTracking.WeekNumber)=29));

where DeltaDays() came from:

Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

    'Get the number of workdays between the given dates

    Dim dbs As Database
    Dim rstHolidays As Recordset

    Dim Idx As Long
    Dim MyDate As Date
    Dim NumDays As Long
    Dim strCriteria As String
    Dim NumSgn As String * 1

    Set dbs = CurrentDb
    Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

    NumSgn = Chr(35)

    MyDate = Format(StartDate, "Short Date")

    For Idx = Int(StartDate) To Int(EndDate)
        Select Case (Weekday(MyDate))
            Case Is = 1     'Sunday
                'Do Nothing, it is NOT a Workday

            Case Is = 7     'Saturday
                'Do Nothing, it is NOT a Workday

            Case Else       'Normal Workday
                strCriteria = "[HoliDate] = " & NumSgn & Format$(MyDate, "yyyy-mm-dd") & NumSgn
               rstHolidays.FindFirst strCriteria
                If (rstHolidays.NoMatch) Then
                    NumDays = NumDays + 1
                 Else
                    'Do Nothing, it is NOT a Workday
                End If

        End Select

        MyDate = DateAdd("d", 1, MyDate)

    Next Idx

    DeltaDays = NumDays

End Function

This query is working 90% correct, except that for records that does not have a DateSent yet (meaning it is still In Process..) it gives an #Error on the WkDays column...

I tried to change the WkDays column that instead of giving that #Error, to just return In Process instead...

I changed the WkDays column to:

WkDays: nz([DateSent],"In Process",IIf((DeltaDays((SELECT StatusDate FROM tblDealStatus WHERE StatusChangeID = 1 AND tblDealStatus.SitusID = tblJobTracking.SitusID),(SELECT StatusDate FROM tblDealStatus WHERE StatusChangeID = 8 AND tblDealStatus.SitusID = tblJobTracking.SitusID))-1)=0,"Same Day",DeltaDays((SELECT StatusDate FROM tblDealStatus WHERE StatusChangeID = 1 AND tblDealStatus.SitusID = tblJobTracking.SitusID),(SELECT StatusDate FROM tblDealStatus WHERE StatusChangeID = 8 AND tblDealStatus.SitusID = tblJobTracking.SitusID))-1))

but it gives an error saying "Wrong number of arguments used with function in query expression..."

Please let me know if there is another way to write the expression on that column...

any help is greatly appreciated...

 

[iuianj07]

Hello again,

okay I was able to re-write the column expression and it looks like it is pulling out the correct results, I wrote:

WkDays: IIf((SELECT StatusDate FROM tblDealStatus WHERE StatusChangeID = 8 AND tblDealStatus.SitusID = tblJobTracking.SitusID) Is Null,"In Process",IIf((DeltaDays((SELECT StatusDate FROM tblDealStatus WHERE StatusChangeID = 1 AND tblDealStatus.SitusID = tblJobTracking.SitusID),(SELECT StatusDate FROM tblDealStatus WHERE StatusChangeID = 8 AND tblDealStatus.SitusID = tblJobTracking.SitusID))-1)=0,"Same Day",DeltaDays((SELECT StatusDate FROM tblDealStatus WHERE StatusChangeID = 1 AND tblDealStatus.SitusID = tblJobTracking.SitusID),(SELECT StatusDate FROM tblDealStatus WHERE StatusChangeID = 8 AND tblDealStatus.SitusID = tblJobTracking.SitusID))-1))

I named this query qryTurnaroundDays...

However, I need another query to group by WkDays, to determine how many records were completed and how long? like how many loans were completed after the Same Day, after 1 day, 2 days etc..

so I wrote this query:

SELECT
 qryTurnaroundDays.WkDays AS [Number of Days],
 Count(qryTurnaroundDays.SitusID) AS Sizings,
 Sum(qryTurnaroundDays.[Property Count]) AS [SumOfProperty Count]
FROM
 qryTurnaroundDays
GROUP BY
 qryTurnaroundDays.WkDays;

however, when I run this query, the database crashed and forcefully exits the database.. does anybody know what could have caused this? is this because of qryTurnaroundDays? Is there a way to fix this?

Any help is greatly appreciated...

Thank you

 

[iuianj07]

I think I forgot to mention one important info...

The database we are working on have tables stored into SQL Server 2008... I've initially created the tables and relationships in Access, then just used SQL Server Move Data Upsizing Wizard...

Is there a possibility that the database crashes when I run this query is because of the backend table? I tried to use an old archived database that still has the tables in Access, and when I ran the query, it didn't crash... so it is safe to assume that it might have something to do with SQL Server?

if yes, then this might be a topic needed to be addressed to a different forum, but if you have an idea on how to fix this, please let me know and I appreciate every help.

Thanks

 

[PHV]

I'd create this function:

Public Function myDeltaDays(StartDate As Date, EndDate)
'Get the number of workdays between the given dates
If Not IsDate(EndDate) Then
  myDeltaDays = "In Process"
  Exit Function
ElseIf StartDate = EndDate Then
  myDeltaDays = "Same Day"
  Exit Function
ElseIf StartDate > EndDate Then
  myDeltaDays = "N/A"
  Exit Function
End If
Dim dbs As Database
Dim rstHolidays As DAO.Recordset
Dim MyDate As Date
Dim NumDays As Long
Set dbs = CurrentDb
Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)
For MyDate = Int(StartDate) To Int(EndDate)
  Select Case Weekday(MyDate)
  Case 2 To 6       'Normal Workday
    rstHolidays.FindFirst "HoliDate=#" & Format$(MyDate, "yyyy-mm-dd") & "#"
    If rstHolidays.NoMatch Then
      NumDays = NumDays + 1
    End If
  End Select
Next MyDate
rstHolidays.Close
myDeltaDays = NumDays - 1
End Function

and then use this query:

SELECT J.SitusID, R.DateReceived, S.DateSent
, IIf(J.ConsolidatedFS=-1 AND J.ConsolidatedRR=-1,1,J.PropertyCount) AS [Property Count]
, myDeltaDays(R.DateReceived, S.DateSent) AS WkDays, J.WeekNumber
FROM ((tblJobTracking J
INNER JOIN (SELECT SitusID,StatusDate AS DateReceived FROM tblDealStatus WHERE StatusChangeID=1
) R ON J.SituID = R.SitusID)
LEFT JOIN (SELECT SitusID,StatusDate AS DateSent FROM tblDealStatus WHERE StatusChangeID=8
) S ON J.SituID = S.SitusID)
LEFT JOIN (SELECT SitusID FROM tblDealStatus WHERE StatusChangeID IN (9,10)
) X ON J.SituID = X.SitusID
WHERE X.SitusID IS NULL AND J.WeekNumber=29

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

 

[iuianj07]

THank you PHV!!

The query works

 

[iuianj07]

Hello PHV,

Thank you for helping me with this one. However, this query is working correctyl before, but then there will be times that when we run the query, it gives an error message:

Run-Time error 3622:

You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.


do you have an idea what causes this error message and how to fix it?

Any help is greatly appreciated.

Thanks!