Query: Issue getting results from Records with Null fields

[June7]

Yes, this can get complicated.

All the query I provided can do is return the max date. It cannot also show the date field and note field because this is an aggregate query. Including those fields requires them to be in the GROUP BY clause. The suggested query will have to be used in another query that joins on job ID and date values. Problem is the Format function results in a string not an actual date. Also, there is the time component to deal with, which my suggested query does not.

qryMaxDates
SELECT tblClientInfo.[Job Number], Max(Format([NoteDate],"yyyy/mm/dd hh:mm:ss")) AS MaxOfNoteDate
FROM tblClientInfo LEFT JOIN tblNotes ON tblClientInfo.[Job Number] = tblNotes.ClientID
GROUP BY tblClientInfo.[Job Number];

qryNotesAdj
SELECT tblNotes.NotedID, tblNotes.NoteDate, tblNotes.User, tblNotes.Notes, tblNotes.ClientID, Format([NoteDate],"yyyy/mm/dd hh:mm:ss") AS DateString FROM tblNotes;

qryMaxDateNote
SELECT [qryMaxDates].[Job Number], [qryMaxDates].MaxOfNoteDate, qryNotesAdj.Notes, qryNotesAdj.NoteDate


FROM qryNotesAdj RIGHT JOIN qryMaxDates ON (qryNotesAdj.ClientID=[qryMaxDates].[Job Number]) AND (qryNotesAdj.DateString=[qryMaxDates].MaxOfNoteDate);

[Modify_inc]

Thank you yet again! I created the three queries and it appears to be working as expected.

Also, thank you for the SQL code! I'm going to study over it and attempt to make sense of it.

Thanks June!