DateDIFF and a Where Clause

arnel_gp 

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