Forum Discussion

Tony2021's avatar
Tony2021
Iron Contributor
Nov 23, 2024
Solved

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","qryFacility...
access
dlookup
Tony2021's avatar
Tony2021
Iron Contributor
Nov 23, 2024

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)

 

arnel_gp's avatar
arnel_gp
Iron Contributor
Nov 25, 2024

You use a Custom function as i have showed you before.

  • Tony2021's avatar
    Tony2021
    Iron Contributor
    Nov 25, 2024

    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. 

Resources