Query: Issue getting results from Records with Null fields

[Modify_inc]

I'm trying to fix my query to display results even if no comments are made in a record.

Currently it will only display records that have a comment, but if a record has no comment, the record is never displayed during a query run, which is causing havoc in my database.

I thought I could use a conditional statement to resolve it, but I get no results when I use the following:

IIf (IsNull ([NoteDate]),[Notes]="No Comments"))

Here is my SQL:

SELECT tblNotes.ClientID, Max(tblNotes.NoteDate) AS MaxOfNoteDate
FROM tblNotes
WHERE (((tblNotes.NoteDate)=IIf(IsNull([NoteDate]),[Notes]="No Comments")))
GROUP BY tblNotes.ClientID;

Without the IF statement everything works fine, unless a record has no comments, which means it has no date for the Max function to process.

I was hoping the IF statement would resolve this by reasoning that if the NoteDate field is Null then it would add the No Comments to the Notes field, thus making the comment record not Null, and thereby displaying the No Comment record in the results. Unfortunately, now I don't get any results!

I think Access hates me

Any suggestions?

Thanks
Mike

[orange]

What exactly is NoteDate?? Sounds like a Date

What do you want this to do in plain English?
(((tblNotes.NoteDate)=IIf(IsNull([NoteDate]),[Notes]="No Comments")))

The IIF has this structure:
IIF(condition, if condition True, if condition False)

[Modify_inc]

What exactly is NoteDate?? Sounds like a Date

What do you want this to do in plain English?
(((tblNotes.NoteDate)=IIf(IsNull([NoteDate]),[Notes]="No Comments")))

The IIF has this structure:
IIF(condition, if condition True, if condition False)

It is a date, it is the date the Note/Comment was recorded.

I have 4 fields for my tblNotes table:

NoteID, NoteDate, Notes, ClientID

NoteID is the Primary key, and ClientID is the foreign key that links the client to the NoteID

Each client can have multiple notes. But when I print reports, I do not want every note for every client printed or displayed, hence why I use the Max(tblNotes.NoteDate)

This works great as it only displays the last most recent note for each client in the report.

But the problem that I have discovered is that clients that do not have any notes are being filtered out completely in my reports. I'm confident the issue is with the above SQL code, because the NoteDate field is Null in these records, thus they are not processed in the query by the Max(tblNotes.NoteDate) function.

I'm just trying to figure out away to make them appear that they do have data and are not Null, and thus show up in my query results.

That is why the IF statement has the string "No Comments". I want it to add the No Comments string to the Notes field if the NoteDate is Null. I was hoping that would resolve the issue, but as I mentioned earlier, now I get NO results.

[June7]

If you want all records, why is there even a WHERE clause?

SELECT tblNotes.ClientID, Max(tblNotes.NoteDate) AS MaxOfNoteDate
FROM tblNotes
GROUP BY tblNotes.ClientID;

That should return a record for each ClientID that has a value in NoteDate and should not matter if Note field is empty.

[Modify_inc]

If you want all records, why is there even a WHERE clause?

SELECT tblNotes.ClientID, Max(tblNotes.NoteDate) AS MaxOfNoteDate
FROM tblNotes
GROUP BY tblNotes.ClientID;

That should return a record for each ClientID that has a value in NoteDate and should not matter if Note field is empty.

You will notice that is exact the same SQL code that I have if you remove the WHERE clause.

The WHERE clause is only there because I am currently experimenting using the IF statement, and it was the only option that would allow my condition to run without popping up an error.

And I don't know if it's just something on my end, but I can guarantee you that the above code as you described will NOT display any records which has no notes entered.

As soon as I go back to them and enter a note and refresh the query, then they are displayed. But they will NOT display if no comment is added to the record.

I just reread you statement again. You stated that if the NoteDate had a value in it, it should not matter if the Note field is empty. That is a true statement, but that is not exactly my issue. If the Notes field is empty then the NoteDate field is also empty and thus the Max(tblNotes.NoteDate) function has no Date to find the Max Date for, resulting in no results for these records.

The Notes field and the NoteDate field work together in my form, if you add a Note, the current Date is added automatically to the NoteDate field. If you don't enter a comment, then there is no entry made in the NoteDate field.

[June7]

What is the structure of this table? Does it have more data than Note and NoteDate? Should these two fields be split out to another table?

Give the query alternate value. Consider:

Max(Format(Nz([StartDate],"0000/00/00"),"yyyy/mm/dd")) AS MaxOfNoteDate

or the following which results in an empty string if field is Null.

Max(Format([StartDate],"yyyy/mm/dd")) AS MaxOfNoteDate

However, if you need these values to be a date for further evaluation, could be an issue because the result is a string, not a date.

[Modify_inc]

What is the structure of this table? Does it have more data than Note and NoteDate? Should these two fields be split out to another table?

Give the query alternate value. Consider:

Max(Format(Nz([StartDate],"0000/00/00"),"yyyy/mm/dd")) AS MaxOfNoteDate

or the following which results in an empty string if field is Null.

Max(Format([StartDate],"yyyy/mm/dd")) AS MaxOfNoteDate

However, if you need these values to be a date for further evaluation, could be an issue because the result is a string, not a date.

I've tried both, but I keep getting a syntax error. I have tried to rewrite them, but with no success.

I assume since you are using the alias MaxOfNoteDate at the end of the expression, I need to put it in the SQL rather than the Criteria field. But either way, I still get a syntax error.

Syntax error in FROM clause

Depending where I put it in the SQL, I get a totally different error message.

Here is where I put it first:

SELECT tblNotes.ClientID, Max(tblNotes.NoteDate) AS MaxOfNoteDate
FROM tblNotes
Max(Format([StartDate],"yyyy/mm/dd")) AS MaxOfNoteDate
GROUP BY tblNotes.ClientID;

To answer you question to the Note Table structure (I mentioned it in my response to Orange in post #3).

I have 4 fields for my tblNotes table:

NoteID, NoteDate, Notes, ClientID

NoteID is the Primary key, and ClientID is the foreign key that links the client to the NoteID

[June7]

Try:

SELECT tblNotes.ClientID, Max(Format([StartDate],"yyyy/mm/dd")) AS MaxOfNoteDate
FROM tblNotes
GROUP BY tblNotes.ClientID;

[Modify_inc]

Try:

SELECT tblNotes.ClientID, Max(Format([StartDate],"yyyy/mm/dd")) AS MaxOfNoteDate
FROM tblNotes
GROUP BY tblNotes.ClientID;

After looking at the SQL you just posted, I cant believe I over looked where I should have placed the Max function. For some reason (I assume the frustration I'm having with this) I was thinking I was adding it as some kind of conditional statement, but obviously it was suppose to replace my previous Max function. DUH!

Anyhow, I used it as you provided, changed the StartDate field name to reflect to my field name, NoteDate. I assume that is what you intended, because if I leave it as StartDate, I am prompted to enter a value for Start Date.

Ran the query, but it still will not display the results for records that do not have any values in the Notes / NoteDate fields.

SELECT tblNotes.ClientID, Max(Format([NoteDate],"yyyy/mm/dd")) AS MaxOfNoteDate
FROM tblNotes
GROUP BY tblNotes.ClientID

Thanks again June for attempting to help someone who is much less knowledgeable in this area than yourself. It's obvious you think this should work, or I would assume you wouldn't be wasting your valuable time with me. I just want you to know I sincerely appreciate it. I try to provide as much detail to assist you or anyone else so you do not have to guess my intentions.
I hope I have accomplished that, if not please let me know.

Mike

[June7]

Yes, did not follow through on editing the sql for posting. I tested the query and it worked on my dataset. If you want to provide db, follow instructions at bottom of my post.

[Modify_inc]

I tried to upload the database but the size limit is NOT 2MBs as it is stated in your signature.

I thought I was doing good because I got it down to 667KBs but the limit is actually 500KBs.

Can I upload it to Dropbox or some other alternative cloud storage that is approved?

[June7]

A zip file of 2MB is allowed. Use Windows Compression.

[Modify_inc]

I forgot I need to zip the file...

I attached a clean version of the Database and added three Job Records to help demonstrate the issue I am having.

There are two Tables:

tblClientInfo
tblNotes

I provided only the query that is presenting me with the issue, though I have more, but they are not part or related to the issue, and I wanted to make sure my database was kept under 2MB. None of the other queries are referenced or called with this query, but if you feel I need to provide them, just let me know. I'm well under the 2MB upload limit at this time.

qryNoteDates

I have two Forms, and as mentioned with the queries, I have other Forms, but they are not referenced or called, but if you feel I need to provide them just let me know also.

frmWorkForm
tblNotes_DatasheetSub1

Job 100 has one Note with a NoteDate value.

Job 101 has no Notes, thus no NoteDate values.

Job 102 has two Notes, with NoteDate values.

You will notice that when you run the qryNoteDates Query, that Job 100 and Job 102 will return in the results, but Job 101 will NOT. At least it will not for me.

Thank you again for taking time to assist me with this issue!

MikeDatabase - Testing.zip

[June7]

Job 101 won't return because there are no records for it in tblNotes. If you want to show 101 even though it has no records, query must join the two tables.

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

[Modify_inc]

Job 101 won't return because there are no records for it in tblNotes. If you want to show 101 even though it has no records, query must join the two tables.

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

That took care of it, thank you! But apparently there's more to it, because I cannot get it show the actual Note data without everything going back to square one.

When I add the Notes and NoteDate fields to the query (because I need to show the last, most recent Note for every Job), it will either show ALL notes for each Job, or no results for Jobs with NO notes, depending on how I use the Left and Right Joins.

How do I get my data from the notes to display along with the query results?

SQL with the NoteDate and Notes fields added:

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

I also have SQL code I wrote earlier that will find the last or most recent Note as similar to this query, but it has the same issues with not displaying Jobs that have no notes. Would it be easier to work with it and have it display the actual Note data?

SELECT tblNotes.ClientID, tblNotes.NoteDate, tblNotes.User, tblNotes.Notes, tblNotes.NotedID
FROM tblNotes
WHERE (((tblNotes.NotedID) In (SELECT TOP 1 NotedID
FROM tblNotes AS Dupe
WHERE Dupe.ClientID = tblNotes.ClientID
ORDER BY Dupe.NoteDate DESC, Dupe.NotedID DESC)))
ORDER BY tblNotes.ClientID, tblNotes.NoteDate, tblNotes.NotedID;

Thanks
Mike