none
Why is my DLast function not working? RRS feed

  • Question

  • Hey guy's I'm trying to have an unbound textbox return the value of my last entry so I can remember where I left off during an audit. I tried a simple function and it's not working, ideally I'd like for it to return [LAST NAME] & ", " & [FIRST NAME] & " (" & [REVIEW DATE] & ")"

    This is what I have so far and it's not even working, I tried adding _ to the space between LAST NAME, still didn't work: 

    =DLast("LAST NAME","qryVADAudits")

    Also tried this: 

    =DLast("[LAST_NAME] & ', ' & [FIRST_NAME] & ' (' & [REVIEW_DATE] & ')' AS LAST_RECORD","qryVADAudits")



    • Edited by InnVis Monday, August 19, 2019 8:39 PM
    Monday, August 19, 2019 8:27 PM

Answers

  • Why not use DMax() to retrieve the Last PK value and then use that value to retrieve the record/data?


    Update

    Now I see you did try.  Please try this version

    =DLookUp("[LAST NAME]", "qryVADAudits", "[pkDailyAuditID]=" & DMax("[pkDailyAuditID]","qryVADAudits"))

    You might also simply wish to validate the DMax()

    DMax("[pkDailyAuditID]","qryVADAudits")
    does it return the PK value you'd expect?


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Tuesday, August 20, 2019 1:01 AM

All replies

  • Try

    =DLast("[LAST NAME]","qryVADAudits")

    or

    =DLast("[LAST NAME] & ', ' & [FIRST NAME] & ' (' & [REVIEW DATE] & ')'", "qryVADAudits")
    You really should not use special characters, including spaces, when naming objects, fields, controls, ...  You may like to read http://www.devhut.net/2017/04/20/access-best-practices-and-troubleshooting-steps/


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net



    Monday, August 19, 2019 9:13 PM
  • Strange, I got a result but it seems to be a random result. The value displayed I know for a fact was not the last one. I edited the query and made sure the primary key field was visible, but it didn't work either. I also tried adding a new record to the database to test it and it did not change the value of the textbox. Any thoughts, do you think it would be best to use the DMax function since the primary key is incremental?
    • Edited by InnVis Monday, August 19, 2019 10:37 PM
    Monday, August 19, 2019 10:33 PM
  • The only really reliable way to determine which row was last entered or edited is to DateTimeStamp the row.  You'll find an example of how to do this in ChangeRecordDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file uses code in the basChangedRecord module to determine if data in a row in a Contacts table has actually been changed rather than merely updated, and assigns the current date/time to a DateTimeStamp column in the table if the row has been newly inserted or changed.  The row with the latest DateTimeStamp value can then be identified in a query, e.g. in my demo:

    SELECT *
    FROM Contacts
    WHERE DateTimeStamp =
        (SELECT MAX(DateTimeStamp)
         FROM Contacts);

    In a form values can then be returned from the query's single row in an unbound text box by calling the DLookup function.

    Ken Sheridan, Stafford, England

    Monday, August 19, 2019 10:43 PM
  • Hey Ken, thanks for sharing! This is a very useful code I will definitively be using, however in this particular instance I don't want to show me last "updated" record, just the very last audit I started. I figured some code could show that data and not really go anywhere just as a reminder.
    Monday, August 19, 2019 10:56 PM
  • I tend to agree with Ken, usually you'd have a field upon which you can use to filter the record to get the 'latest' one, a date field, a PK field....  If you want the last audit, do you not have an Audit Date field upon which you could retrieve the Max Audit Date entry?

    Another option would be to add some simple code to your form's close event to store the primary Key value of the record you're on.  Then simply read that value and retrieve the corresponding data.


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, August 20, 2019 12:31 AM
  • I also tried to validate the value by putting a criteria to filter out by primary key where the largest value is what I want since the key is incremental, still gave me the wrong value.

    =DLookUp("[LAST NAME]","qryVADAudits",DMax("[pkDailyAuditID]","qryVADAudits"))

    also this one

    =DLookUp("[LAST NAME]","qryVADAudits",DLast("[pkDailyAuditID]","qryVADAudits"))
    I suspect it's because my query is ordered by date, since it keeps returning the first value on the list with these two codes.

    • Edited by InnVis Tuesday, August 20, 2019 12:35 AM
    Tuesday, August 20, 2019 12:33 AM
  • Why not use DMax() to retrieve the Last PK value and then use that value to retrieve the record/data?


    Update

    Now I see you did try.  Please try this version

    =DLookUp("[LAST NAME]", "qryVADAudits", "[pkDailyAuditID]=" & DMax("[pkDailyAuditID]","qryVADAudits"))

    You might also simply wish to validate the DMax()

    DMax("[pkDailyAuditID]","qryVADAudits")
    does it return the PK value you'd expect?


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Tuesday, August 20, 2019 1:01 AM
  • YES YES YES! Tested it multiple times, now I gotta clean my database from all the dummy records, LOL.
    Tuesday, August 20, 2019 1:12 AM