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
Hi Arnel / Ken
QryA is as follows. Its long I know. I will try to format it for better readability.
I hope it makes sense.
Just to recap, I am needing the day count between tblDraws.FundingDate and LastBdMo (the last bus day of the month. Just weekday. Not considering holidays) where tblDraws.Type = tblDraws.Type (this is an ID) and I think I will need to add a date condition as well: where tblDraws.[FundingDate] < tblDraws.[FundingDate]
I dont know if its better to insert the codes using the button or just simply pasting. I am using both.
QryA:
SELECT
qryDrawsDecliningCumDrawn.FundingDate, tblFacility.Commitment, [tblfacility].[commitment]-Format(Nz(DSum("SumOfAmount","qryDrawsDecliningCumDrawn","ID=" & [qryDrawsDecliningCumDrawn].[ID] & " And [FirstOfIDDetailpk] < " & Nz([qryDrawsDecliningCumDrawn].[FirstOfIDDetailpk],0)),0),"Currency") AS OpenBal, qryDrawsDecliningCumDrawn.SumOfAmount AS SumDrawn, [OpenBal]-[SumDrawn] AS EndBalAvail, [Commitment]-[EndBalAvail] AS CumDrawn, qryDrawsDecliningCumDrawn.Type, qryDrawsDecliningCumDrawn.ID, qryDrawsDecliningCumDrawn.DrawsIDfk, [sumdrawn]*[AllInRateDD]*[DayCountDD]/360 AS FeeDD, qryInterest.AllINRateDD, Abs(DateDiff("d",[qryDrawsDecliningCumDrawn].[FundingDate],[LastBdMo])) AS DayCountDD, fncLastBusinessDayThisMo([qryDrawsDecliningCumDrawn].[FundingDate]) AS LastBdMo, fncLastBusinessDay([qryDrawsDecliningCumDrawn].[FundingDate]) AS LastBdNxtMo, qryInterest.CLBLiborRate, qryInterest.AllInRateCLB, qryDrawsDecliningCumDrawn.TypeIDfk
FROM
qryInterest RIGHT JOIN ((tblDraws INNER JOIN qryDrawsDecliningCumDrawn ON tblDraws.ID = qryDrawsDecliningCumDrawn.ID) INNER JOIN tblFacility ON qryDrawsDecliningCumDrawn.FacIDfk = tblFacility.ID) ON qryInterest.DrawIDfk = qryDrawsDecliningCumDrawn.DrawsIDfk
ORDER BY
qryDrawsDecliningCumDrawn.FundingDate, qryDrawsDecliningCumDrawn.TypeIDfk;
this is what it looks like:
qryDrawsDecliningCumDrawn:
SELECT
tblDrawsDetails.DrawsIDfk, Sum(tblDrawsDetails.Amount) AS SumOfAmount, tblDrawTypeDropBox.Type, tblFacility.ProjID, tblDrawTypeDropBox.ID, tblFacility.TypeIDfk, tblDrawsDetails.FacIDfk, First(tblDrawsDetails.IDDetailpk) AS FirstOfIDDetailpk, tblDraws.FundingDate, Year([FundingDate])*12+DatePart('m',[FundingDate])-1 AS MonthYrSort
FROM
tblDraws INNER JOIN ((tblDrawsDetails LEFT JOIN tblFacility ON tblDrawsDetails.FacIDfk = tblFacility.ID) LEFT JOIN tblDrawTypeDropBox ON tblFacility.TypeIDfk = tblDrawTypeDropBox.ID) ON tblDraws.ID = tblDrawsDetails.DrawsIDfk
GROUP BY
tblDrawsDetails.DrawsIDfk, tblDrawTypeDropBox.Type, tblFacility.ProjID, tblDrawTypeDropBox.ID, tblFacility.TypeIDfk, tblDrawsDetails.FacIDfk, tblDraws.FundingDate, Year([FundingDate])*12+DatePart('m',[FundingDate])-1, tblDraws.Type
HAVING (((tblDraws.Type)<>4));
below is the function
fncLastBusinessDayThisMo
' arnelgp - expert on ms tech
Public Function fncLastBusinessDayThisMo(ByVal FundingDate As Date) As Date
Dim dte As Date
dte = DateSerial(Year([FundingDate]), Month([FundingDate]) + 1, 0)
Do Until InStr(1, "Sat/Sun", Format$(dte, "ddd")) = 0
dte = DateAdd("d", -1, dte)
Loop
fncLastBusinessDayThisMo = dte
End Function
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 FunctionHi Arnel, thank you very much! that worked perfectly!
I guess I dont need the criteria on [Type] or [FundingDate] as I mentioned because it looks good.
thanks again both of you...greatly appreciate the expert help!
You might find the routines of interest in Calendar.zip in my public databases folder at:
https://1drv.ms/f/c/44cc60d7fea42912/EhIppP7XYMwggESpAAAAAAABaDKZCllSuweYBPJ5zKa3cg
This little file allows you to create a number of types of auxiliary calendar tables, and includes an option to create a last working day of each month table. It also has the option to remove public holidays from the table, in which case the date is moved back to the last working day preceding the holiday.
Armed with a table like this a task like yours then becomes a simple one of joining any table containing dates to the calendar table on the month and year of the date. To return the difference is then a simple subtraction of one date from the other. No procedural code is required, making the query portable.
If you wanted to return the difference in working days, you can find functions for this in Workdays.zip in the same OneDrive folder.
- thank you Ken!
