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:
where DeltaDays() came from:
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:
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...
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:
Code:
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:
Code:
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:
Code:
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...