Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    allanmurrell is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    7

    Return Latest Date

    Hi



    I am new to Queries. This simple database is about the recording of Student Patient vaccinations in school.

    I have an intermediate table called intbl_School_Patient which links to tbl_Patient and tbl_Schools.

    In the intbl_School_Patient table I have a field for Since_Date which is the date the student/patient started at that school.

    When I am in the form frm_Students, I click on a button that opens a form where I can add a new school that the student/patient is now attending. When I close that form, I want to pull just the Name of the School that has the latest date against it for that Student/Patient and add it to a text box (actually a label) on my form. I have created a query but it is pulling back all the schools and dates for some reason.

    Code:
    SELECT tbl_Schools.School_Name, intbl_School_Patient.Since_Date
    FROM tbl_Schools INNER JOIN intbl_School_Patient ON tbl_Schools.School_ID = intbl_School_Patient.School_ID
    GROUP BY tbl_Schools.School_Name, intbl_School_Patient.Since_Date, intbl_School_Patient.School_Patient_ID, intbl_School_Patient.School_ID, intbl_School_Patient.Patient_ID
    HAVING (((intbl_School_Patient.Patient_ID)=[Forms]![frm_Patient2]![txt_Patient_ID]))
    ORDER BY intbl_School_Patient.Since_Date DESC;
    Any help would be appreciated

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps:
    Code:
    SELECT TOP 1 tbl_Schools.School_Name, intbl_School_Patient.Since_DateFROM tbl_Schools INNER JOIN intbl_School_Patient ON tbl_Schools.School_ID = intbl_School_Patient.School_ID
    GROUP BY tbl_Schools.School_Name, intbl_School_Patient.Since_Date, intbl_School_Patient.School_Patient_ID, intbl_School_Patient.School_ID, intbl_School_Patient.Patient_ID
    HAVING (((intbl_School_Patient.Patient_ID)=[Forms]![frm_Patient2]![txt_Patient_ID])) ORDER BY intbl_School_Patient.Since_Date DESC;
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    allanmurrell is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    7
    Thank you Bob. That answered my initial question perfectly.

    I am having to run a query in a window to see the results but I wondered if there is a way I can take the a value of a particular field in the query and pass it to a text box in a form.

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by allanmurrell View Post
    Thank you Bob. That answered my initial question perfectly.

    I am having to run a query in a window to see the results but I wondered if there is a way I can take the a value of a particular field in the query and pass it to a text box in a form.
    You could use the DLookup() function (with some criteria) to return data from the query. Post back if you need help with it.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    allanmurrell is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    7
    Hi Bob

    Yes, I think I may need some help with that if you wouldnt mind. That would be greatly appreciated.

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Try this:

    Save the SQL statement I posted earlier as a saved query and name it qryLastSchool.
    Put a text box on your form and call it txtLastSchool.
    Use the following line of code when you close the other form. You could put it in the On Click event of the button that you use to close the form.
    frm_Students.txtLastSchool=DLookup("School_Name"," qryLastSchool")
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    allanmurrell is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    7
    Hi Bob

    A couple of the control names have changed, but this is returning an 'Object Required' error..

    Code:
    Private Sub Form_Close()
        frm_Patient2.txt_School = DLookup("School_Name", "qry_Current_School")
    End Sub

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Can you post a copy of the db with all sensitive data removed
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  9. #9
    allanmurrell is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    7
    I didnt know I could attach files in here. How would I do that ?

  10. #10
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  11. #11
    allanmurrell is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    7
    Here you go Bob. Thank you for this.
    Attached Files Attached Files

  12. #12
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Sorry, I've just realised my mistake. Try:
    Code:
    Private Sub Form_Close()
        Forms!frm_Patient2.txt_School = DLookup("School_Name", "qry_Current_School")
    End Sub
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  13. #13
    allanmurrell is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    7
    Thank you, Bob. That all works perfectly now.

  14. #14
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by allanmurrell View Post
    Thank you, Bob. That all works perfectly now.
    You're welcome. Always glad to help if I can.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  15. #15
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Allan

    The attached is another way of displaying the Schools that the Patient has attended.
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-31-2018, 08:47 AM
  2. latest date
    By slimjen in forum Programming
    Replies: 11
    Last Post: 09-12-2014, 08:46 PM
  3. Value of latest date
    By v!ctor in forum Queries
    Replies: 3
    Last Post: 02-27-2013, 03:48 PM
  4. Query Expression to return only the latest data
    By Brinleigh217 in forum Queries
    Replies: 6
    Last Post: 03-23-2012, 02:53 PM
  5. How do you get the latest date?
    By radicrains in forum Queries
    Replies: 6
    Last Post: 10-27-2010, 08:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums