Forum Discussion
Dlookup with <=
i made new function, see your query now.
Where multiple rows match the criterion the DLookup function will return a value from only one of those rows, essentially an arbitrary value as a set has no concept of order. If you want to return the value at the latest date on or prior to a certain date then you should need to do this in two stages, firstly identifying the latest date prior to criterion date, and then returning the balance at that date, i.e.
DLookup("Balance","qryFacilityBal", "ProjIDfk=" & tblDraws.ProjID & " And IDFacfk =" & tblFacility.ID & " And FundingDate =#" & Format(DMax("FundingDate", "qryFacilityBal", "ProjIDfk=" & tblDraws.ProjID & " And IDFacfk =" & tblFacility.ID),"yyyy-mm-dd") & "#"))
Note the use of the ISO standard for date notation of YYYY-MM-DD to ensure international unambiguity.
However, to ensure the correct results you should firstly constrain the data with the following in the table definition:
1. Disallow in the date columns by setting the column's Requred property to True (Yes).
2. Disallow date/time values with a non-zero time of day element by means of the ValidationRule property, setting this to Int([NameOfColumnGoesHere])
HI Ken, that is amazing. I am glad to know that it was not me and I simply needed to approach it differently with Dmax.
I did however get a syntax error of an additional parenthesis at the very end (pic below) so I removed it and then ran it however a #error is returned for all the rows so not sure if it has something to do with the parenthesis or maybe something else? Please let me know what you think is next. Also I did not make that change of Required property to True (yes) since there are some cases where the date is actually null since there was not a funding but still there is a record but without a FundingDate. Not sure if changing the property will create some kind of issue if there are nulls. thank you sir...
To replicate the DLookup function call in a function would, I think, be like this:
Public Function GetBalance(dtmFundingDate As Date, lngProjID As Long, lngFacID As Long) As Currency
Dim strCriteria As String
strCriteria = "DateAmend <=#" & Format(dtmFundingDate, "yyyy-mm-dd") & "# And " & _
" ProjIDfk = " & lngProjID & " And IDFacfk= " & lngFacID & " And [DateAmend] = #" & _
Format(DMax("DateAmend", "qryFacilityBal", "ProjIDfk=" & lngProjID & _
" And IDFacfk =" & lngFacID & " And DateAmend <= #" & _
Format([Funding Date],"yyyy-mm-dd") & "#"),"yyyy-mm-dd") & "#"GetBalance = DLookup("Balance","qryFacilityBal", "ProjIDfk=" & lngProjID & _
" And IDFacfk =" & lngFacID & " And " & strCriteria)End Function
It would be necessary to include error handling in the function to cater for the DateAmend being earlier than the Funding date. The error handler would essentially be the code in the Else part of the If…End If construct in the function I originally gave you. You'd need to do some debugging to determine by trial and error the Err.Number value being raised, by calling the function in the Immediate window, passing literal values for each of the three arguments, and then make that code conditional on that value in the error handler.
HI Ken, thank you very much for the response. I will try and figure out the error handler as you mention.
Do you happen to know why I get a "Datetype mismatch error"? I hovered over the fields and I lngProjID, lngFacID were numbers showing on the hover and dtmFundingDate was a date too. Let me know what you think when you have a sec. thank you very much.
I've tested the expression in a query in one of my demo files, and, after removing the surplus closing parenthesis (mea culpa!) it returns the expected results. Looking closely at the image in your post, though, it looks like the Funding Date column name includes a space, which I'd missed, so will need to be wrapped in square brackets in the expression, [Funding Date].
However, on reflection, I don't think the logic of the expression was right. I'd assumed we were dealing with two instances of the same set, but from the dates in the two result tables I can see that's not the case. I think the following is probably closer to the mark:
DLookup("Balance","qryFacilityBal", "ProjIDfk=" & tblDraws.ProjID & " And IDFacfk =" & tblFacility.ID & " And [DateAmend] = #" & Format(DMax("DateAmend", "qryFacilityBal", "ProjIDfk=" & tblDraws.ProjID & " And IDFacfk =" & tblFacility.ID & " And DateAmend <= #" & Format([Funding Date],"yyyy-mm-dd") & "#"),"yyyy-mm-dd") & "#")
As regards the Nulls, can you exclude the rows from the query's result table by restricting it in its WHERE clause?
Hello Ken, that was the fix! I do however have #errors for when the qryFacilityBalance.DateAmend is <tblDraws.FundingDate. (there actually is not a space. I used an alias(i thk thats what its called).
Let me back up a second. This question is really a follow up to another question you answered by developing a function that was a really crafty response.
Dlookup - 3 criteria | Microsoft Community Hub
After looking over the data in detail, the function had the same issue when the dates match betweeen qryFacilityBalance.DateAmend and tblDraws.FundingDate and that is why I posted this question since I thought I could find the fix using dlookup. I fiddled around with that function for awhile with changing <= to < and tried > and >= but I couldnt get it to return the correct value when the dates are equal (it returned the correct value in every other case).
Now, knowing the dlookup function in this question DOES return the correct value (although I do have the #errors as stated above when qryFacilityBalance.DateAmend is <tblDraws.FundingDate) it might be easier to modify the function since I do not have the #error issue in the function since I know the codes take care of it (with the Else statement) as I pointed it out in the other question.
I am going to paste that function below
Do you see where it needs to be modified to return the same as the dlookup does?
The dlookup posted in the response above changed the tblDraws.FundingDate to qryFacilityBal.DateAmend and that was the fix for when the dates are equal so maybe the function might needs to be tweaked since that seemed to be the fix at least in the dlookup. I dont really see where to make that fix in the function.
here is the output showing the dlookup output, which is now correct, vs the function output
thank you very much for staying with me on this question. I sincerely appreciate your help. Please let me know if you have any questions.
Public Function GetBalance(dtmFundingDate As Date, lngProjID As Long, lngFacID As Long) As Currency 'for finding the facility commitment balance if any step up/down). qryFacilityBal. '11-10-24 Ken Sheridan expert on MS Tech (really smart) 'use like GetBal: GetBalance([tblDraws].[FundingDate],[tblDraws].[ProjID],[tblFacility].[ID]) Dim varDateAmend As Variant Dim strCriteria As String strCriteria = "DateAmend <=#" & Format(dtmFundingDate, "yyyy-mm-dd") & "# And " & _ " ProjIDfk = " & lngProjID & " And IDFacfk= " & lngFacID varDateAmend = DMax("DateAmend", "qryFacilityBal", strCriteria) If Not IsNull(varDateAmend) Then strCriteria = "DateAmend = #" & Format(varDateAmend, "yyyy-mm-dd") & "# And " & _ " ProjIDfk = " & lngProjID & " And IDFacfk = " & lngFacID GetBalance = DLookup("Balance", "qryFacilityBal", strCriteria) Else strCriteria = "ProjIDfk = " & lngProjID & " And IDFacfk= " & lngFacID varDateAmend = DMin("DateAmend", "qryFacilityBal", strCriteria) strCriteria = "DateAmend = #" & Format(varDateAmend, "yyyy-mm-dd") & "# And " & _ " ProjIDfk = " & lngProjID & " And IDFacfk = " & lngFacID GetBalance = DLookup("Balance", "qryFacilityBal", strCriteria) End If End Function