Forum Discussion
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 and the query runs but the column is returning a #error.
I assume I cant add a where condition but maybe an expert has another idea.
DayCount: Abs(DateDiff("d",[FundingDate],[LastBdMo],"TypeIDfk = " & [tblFacility].[TypeIDfk]))
thank you...
let me know if not clear.
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];
Tony2021 you can create a Public function in a Module and call it within your query:
Public Function fnDaysBetweenFundingAndBid(ByVal FK As Long) As Variant ' put the name of your table below Const TABLE_NAME As String = "table3" Static rs As DAO.Recordset Dim dt1 As Variant Dim dt2 As Variant If rs Is Nothing Then Set rs = CurrentDb.OpenRecordset(TABLE_NAME, dbOpenSnapshot, dbReadOnly) End If fnDaysBetweenFundingAndBid = Null With rs .Requery .FindFirst "TypeIDFk = " & FK If Not .NoMatch Then dt1 = !FundingDate dt2 = !LastBdMo If IsDate(dt1) And IsDate(dt2) Then fnDaysBetweenFundingAndBid = DateDiff("d", dt1, dt2) End If End If End With End Functionto use it in your query:
DayCount: fnDaysBetweenFundingAndBid([TypeIDfk])
thats amazing Arnel.
I am not sure if it would be better to to use the query instead of the table name. The tbldDraws.FundingDate is in tblDraws, the LastBdMo is a function and tblFacility.TypeIDfk is in tblFacility and this is all combined into a query qryA (not the real name...just for simplicity).
You can see my error below. I know the red box is not correct since I am using tblDraws as teh table.
I guess the first question is whether its better to use the name of the query instead of a table name.
I hope this makes sense. Let me know. thank you
Are you trying to return the date difference over a subset of rows? If so you can do this by means of a correlated subquery. The following is an example, using the Orders table from Northwind, which returns the number of days from the date of each customer's first order to the date of their current order:
SELECT CustomerID, OrderDate,
INT(OrderDate) - (SELECT INT(MIN(OrderDate))
FROM Orders AS O2
WHERE O2.CustomerID = O1.CustomerID) AS DayCount
FROM Orders AS O1
ORDER BY CustomerID, OrderDate DESC;
Note that the INT function is used here because the OrderDate values include non-zero times of day. Assuming that you don't want the returned DayCount values to include fractions of a day, the subtraction uses integer values. If the values were to be constrained to integers (date only) by means of the column's ValidatioRule property, the INT function would be unnecessary of course. Alternatively the DateDiff function could be used to ignore the fractional part of the DateTime data type values.
If, on the other hand, you want to return the date difference between two values of DateTime data type in a single row, then you can use a simple DLookup function call:
DayCount:DLookup("Abs(FundingDate - LastBdMo)","TableNameGoesHere","TypeIDfk = " & [TypeIDfk])
Hi Ken, thank you.
To quickly answer your question, yes it is a SUBSET of rows (not a single row).
I am not exactly sure how to use the select query.The LastBdMo uses a function like this:
LastBdNxtMo: fncLastBusinessDay([qryDrawsDecliningCumDrawn].[FundingDate])noting that [FundingDate] is in tblDraws.
this is in my QryAThis is how I modified it (I get a syntax. pic below):
in the below Type is an ID. I should have named it TypeIDfk but I did not.
Not sure if I can use the function like I am.
SELECT ID, FundingDate, Type
INT(FundingDate) - (SELECT INT(MIN(fncLastBusinessDay(FundingDate))
FROM tblDraws AS O2
WHERE O2.Type = O1.Type) AS DayCount
FROM tblDraws AS O1
ORDER BY ID, FundingDate DESC;here is the syntax (I assume its a paren and I tried to add a paren in a few places to no avail):
I do see quite possibly another issue. I would need to consider another criteria for tblDraws.FundingDate< tblDraws.FundingDate so essentially having 2 criteria instead of only Type = Type
I do realize this is difficult without the database in front of you. Apologies for that.
thank you for the help! I greatly appreciate it. If I had to do it all over again I would have chosen the computer route instead of finance. Its amazing the level of experience you guys have.
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 BusinessDaysCalendarWHERE BusinessDaysCalendar.[Type] = tblDraws.[Type]) AS DayCount
FROM tblDraws
ORDER BY ID, FundingDate DESC;
