Forum Discussion
Solved
DateDIFF and a Where Clause
Hello Experts, I am trying to calculate a day count between 2 dates [FundingDate],[LastBdMo] but I need to add a where condition. I am not sure if I can do this? I have added the blue below an...
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
Can you please attach a sample file or share the file via onedrive or google-drive? You need to place where condition outside DateDiff() function like DayCount: Abs(DateDiff("d",[FundingDate],[LastBdMo])) WHERE TypeIDfk = [tblFacility].[TypeIDfk];