Forum Discussion
Dlookup with <=
Hello Experts,
I am having a hard time with not understanding why the dlookup is not returning what I think it should.
I have the following:
CommitBalDlook: Format(DLookUp("Balance","qryFacilityBal","ProjIDfk=" & [tblDraws].[ProjID] & " And [IDFacfk] =" & [tblFacility].[ID] & " And nz([DateAmend],0) <=" & Format$(Nz([tblDraws].[FundingDate],0),"\#mm\/dd\/yyyy\#")),"Standard")
so if [qryFacilityBal].[DateAmend] is <= to [tblDraws].[FundingDate] it should return the [qryFacilityBal].[Balance] if the dates are equal but it looks like it is returning the [qryFacility].[Balance] that is less than in the case where they are equal (please refer to the pic below). I also tried removing Nz on DateAmend but that did not change anything but there are no nulls anyways. Possibly the format on tblDraws.FundingDate has something to do with it (the "\#mm\/dd\/yyyy\#" but I dont think its the problem).
here is a pic:
DateAmend and FundingDate are both Short Date format
in the above, you can clearly see that the pic on the right (where the dlookup is) is returning 2.34B where the way I understand it, it should return 2.23B since the dates are equal (10/24/23) on [DateAmend] and [FundingDate]. What is odd is that if I change the criteria from <= to just = then it does return 2.23B but then all the other records are null where there is not a match so I cant use =.
Do you see anything wrong with my Dlookup?
I am sure I am missing something. I have spent hours fiddling around on this but not getting anywhere.
grateful for the help.
i made new function, see your query now.
11 Replies
i made new function, see your query now.
Wow Arnel that worked perfectly! Very crafty indeed. I really appreciate your help! Have a good day...and thank you once again for hanging in there with me.
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...
