Forum Discussion
DateDIFF and a Where Clause
Tony2021 , you replace:
..
Abs(DateDiff("d",[qryDrawsDecliningCumDrawn].[FundingDate],[LastBdMo])) AS DayCountDD
with:
Abs(DateDiff("d",[qryDrawsDecliningCumDrawn].[FundingDate], fncLastBusinessDayThisMo([qryDrawsDecliningCumDrawn].[FundingDate])) As DayCountDD
Also change the function to this:
Public Function fncLastBusinessDayThisMo(ByVal FundingDate As Date) As Date Static dte_static As Date Static dte_ans As Date Dim dte As Date dte = DateSerial(Year([FundingDate]), Month([FundingDate]) + 1, 0) If dte = dte_static Then fncLastBusinessDayThisMo = dte_ans Exit Function End If dte_static = dte Do Until InStr(1, "Sat/Sun", Format$(dte, "ddd")) = 0 dte = DateAdd("d", -1, dte) Loop dte_ans = dte fncLastBusinessDayThisMo = dte End Function
A subquery would remove the need to call the fncLastBusinessDay function. To do it all in SQL by means of a subquery, you'd probably need an auxiliary BusinessDaysCalendar table. Auxiliary tables can be extremely useful for many things (Joe Celko has a whole chapter on them in his excellent book 'SQL for Smarties' – full disclosure, I'm cited in it). In this case you'd use one along the following lines:
SELECT ID, FundingDate, [Type],
FundingDate - (SELECT MAX(BusinessDay)
FROM BusinessDaysCalendar
WHERE BusinessDaysCalendar.[Type] = tblDraws.[Type]) AS DayCount
FROM tblDraws
ORDER BY ID, FundingDate DESC;
Hi Ken,
Sorry but I mispoke. The function is simply returning the LAST business day of the month meaning if the last day is a Saturday then make it Friday (not considering any holiday....just last business day). I am looking for the count from the FundingDate to the last business day of the month but considering the [Type] criteria.
Does that change your response?