none
Why is my function not working? RRS feed

  • Question

  • Hey guys, can I please have a second pair of eyes look at this line? I have a form that is opened every day to create an audit in a particular profile every day. The profile has an id called FIN that makes it unique. Anyways, I have a field in this form that is for auditors to write notes to themselves about that profile and I want that every time I create a new audit record, I want the field in that form to copy forward the data from the last audit's note field. It worked well for a while with a simpler version of the code, but with data being edited and deleted over time I'm loosing some notes and I would like to create a validation to ensure I always get the previous audit note.

    Here's the code that worked for a while:

    Me.txtReviewerNotes.Value = DLast("ReviewNotes", "qryVADAudits", "[FIN #]='" & Me.PatientFIN & "'")


    and here's what I'm trying to do, but it's not working - I tried the DLast and DMax function, if I use " " over these functions it gives me an error, but accepts ' ' instead:

    Me.txtReviewerNotes.Value = DLast("ReviewNotes", "qryVADAudits", "[FIN #]='" & Me.PatientFIN & "' And DLast('[REVIEW DATE]', 'qryVADAudits')")

    I'm starting to suspect there is a bigger flaw at play, I think it's only returning the last value in my audits ONLY if the last value matches the FIN. Any ideas on fixing this?



    • Edited by InnVis Wednesday, August 21, 2019 2:59 PM EDITS
    Wednesday, August 21, 2019 2:49 PM

Answers

  • Try this:

    Me.txtReviewerNotes.Value = DLookup("ReviewNotes", "qryVADAudits", "[FIN #]='" & Me.PatientFIN & "' AND [REVIEW DATE]=#" & Nz(DMax("[REVIEW DATE]", "qryVADAudits", "[FIN #]='" & Me.PatientFIN & "'"), #1/1/1900#) & "#")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by InnVis Wednesday, August 21, 2019 4:38 PM
    Wednesday, August 21, 2019 4:03 PM

All replies

  • Does the underlying table have an AutoNumber field? (Every table should have such a field)

    If so, you could use

    Me.txtReviewerNotes.Value = DLookup("ReviewNotes", "qryVADAudits", "ID=" & DMax("ID", "qryVADAudits", "[FIN #]='" & Me.PatientFIN & "'"))

    where ID is the name of the AutoNumber field.

    If not, can there be multiple notes for the same PatientFIN on the same day?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, August 21, 2019 3:17 PM
  • Tried it on 4 different records. 2 worked, 1 caused an error (because it's never had an audit in the past so a null value for the AutoNumber), and 1 of them didn't work at all although there are records in the particular field.

    Would it be possible to have it find the record with the matching [FIN #] and return the value of the last (or max) field in [REVIEW DATE], since I'm frequently bringing old records from previously used excel files into the MS Access database, the AutoNumber might be greater in older records than even newer ones.

    OK, I fixed the  issue with data not coming through for the one record. The new issue is that if the record didn't have a note before, it's returning the note from the previous record (or random record, I can't tell), and also if there is no previous audits I get an error as well. How do I add a clause to return nothing if the criteria is not met or the record is null (because there has never been an audit on this profile yet)?

    This is what I'm using now:

    Me.txtReviewerNotes.Value = DLookup("ReviewNotes", "qryVADAudits", "[REVIEW DATE] =#" & DMax("[REVIEW DATE]", "qryVADAudits", "[FIN #]='" & Me.PatientFIN & "'") & "#")

    • Edited by InnVis Wednesday, August 21, 2019 3:50 PM EDITS
    Wednesday, August 21, 2019 3:37 PM
  • Try this:

    Me.txtReviewerNotes.Value = DLookup("ReviewNotes", "qryVADAudits", "[FIN #]='" & Me.PatientFIN & "' AND [REVIEW DATE]=#" & Nz(DMax("[REVIEW DATE]", "qryVADAudits", "[FIN #]='" & Me.PatientFIN & "'"), #1/1/1900#) & "#")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by InnVis Wednesday, August 21, 2019 4:38 PM
    Wednesday, August 21, 2019 4:03 PM
  • Ugh, that worked like a charm! I feel dumb for not remembering the Nz function to call a NULL value when the data criteria is not met. Thank you thank you thank you!
    Wednesday, August 21, 2019 4:38 PM