Forum Discussion
Dlookup with <=
i made new function, see your query now.
the pic didnt want to attach as a screen shot paste. I hope the below is viewable. (after I attached as an attachment I then tried to paste and it pasted. (also, there is no button (...) to edit the original question or else I would have posted the pic in the original question)
You use a Custom function as i have showed you before.
Hi Arnel, yes I do see that your function does work and accounts for dates being out of order. The problem I have is that the db I initially posted was too simple and I cant make that function you provided work in my production db since I dont know the edits to make. I have tried to adjust it but its too complicated for me.
i did however spend some time making an exact replica of my production db and pared it down to only necessary tables and queries.
I have attached it if you care to take a peek. The query to look at is only qryDrawsDecliningBal and qryFacilityBal (qryDrawsDecliningCumDrawn can be ignored and everything else). It is filtered for tblFacility = 1.
If you scroll to the last record on the qryDrawsDecliningBal. (on the Dlook field) you can see the value is 2.2B (pic below), which is correct since the [FundingDate] equals 10/24/2023 (but there are records with #error since they are outside of the dates at the top) but the function field says 2.3B, which is not correct.
I pasted your function into the db.
here is the output qryDrawsDecliningBal:
thank you in advance sir. I am grateful for your response.
I dont see that the file attached. trying again. It does show it uploaded but I dont see the file when viewing the question. Maybe it will show now.
well it didnt appear to upload once posted the reply.
Please follow link to my onedrive:
BalancesDatesFx_Nov252024.accdb
thank you. sorry for the confusion.
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.
