dlookup

4 Topics

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.
Solved
Tony2021
Nov 26, 2024 Place Access
151Views
0likes
11Comments
Dlookup - 3 criteria
Hello Experts, I am trying to use Dlookup with 3 criteria. The attached excel file is clearer but I will try to explain. What I need to return is the qryFacilityBal.[Balance] where "ProjIDfk=" & [tblDraws].[ProjID] & " And [IDFacfk] =" & [tblFacility].[ID] and [DateAmend] < " & [tblDraws].[FundingDate]. The 3rd criteria is what I am having problems with. I don’t think it’s a simple < criteria since it seems more like a BETWEEN statement. Maybe an expert knows exactly what I am trying to do. here is the criteria: If the [FundingDate] is < [qryFacilityBal].[DateAmend] then use the MIN [qryFacility].[Balance]. If the [FundingDate] is <= MAX [qryFacilityBal].[DateAmend] then use the MAX [qryFacility].[Balance]. If its BETWEEN I am not sure how that would be coded. Here is my dlookup clause (red part is the issue): FacAmtDlook: Format(DLookUp("Balance","qryFacilityBal","ProjIDfk=" & [tblDraws].[ProjID] & " And [IDFacfk] =" & [tblFacility].[ID] & " And nz([DateAmend],0) < " & Format$(Nz([tblDraws].[FundingDate],0),"\#mm\/dd\/yyyy\#")),"Standard") Please see attached xl file. The example is clearer but I have a screen shot below. grateful for the help. I hope its clear. The excel file I think is easier to follow. Let me know if not clear.
Solved
Tony2021
Nov 11, 2024 Place Access
128Views
0likes
16Comments
Dlookup a Rate based on Dates
Hello Experts, I am trying to lookup a rate in a qry (qryPricing) and it returns the rate but its not according to the Date parameters (the <=). I think there is something wrong after the <= sign below. The DateStart and DateEnd are mm/dd/yyyy format while the [dte] field is formatted yyyy-mm. I am not sure if that is the problem. I tried to change the yyyy-mm tomm/dd/yyyy but that didnt fix it. Rate: Nz(DLookUp("Rate","qryPricing","ProjID=" & [ProjID] & " And [DateStart] <= #" & Format$([dte],"yyyy-mm") & "#"),0) in every case the rate of 1.375% is being returned even if the dates are outside of the date start and date end for 1.375%. qrypricing: yyyy-mm (I use this format to sort the data correctly). maybe the formula is just simply not correct. Let me know if additional clarification is needed. thank you.
Solved
Tony2021
Sep 18, 2022 Place Access
2.2KViews
0likes
9Comments
Syntax error - IIF and Dlookup
Hello Experts, I have a union query and I am trying to add a dlookup to it. I am getting a syntax but I dont see where it is? think its on the dlookup part and I have been adding parenthesis but getting nowhere. below is only the first column of the union query. UNION ALL SELECT IIf([GroupDate] Like "Coming Due" & "*",IIf(Nz([Amount],0)<0,[Amount]*-1,[Amount]), dlookup("Balance Euro","tblFXParent","IDFXParent =" & IDParentfk) as TEST, thank you.
Solved
Tony2021
Sep 06, 2022 Place Access
1.4KViews
0likes
5Comments