Comparison Error with NULL

[kwhite100]

Hello and thank you again for the assistance.

I am running the code below and it is returning back the desired results that I want:

SELECT ProjectId, d.Code,WeekStart,Hours,NOW() AS RecordDate
FROM ((tmpSparkleActualsByDisciplinekntv a
LEFT JOIN Discipline d
ON a.Discipline = d.DisciplineName)
LEFT JOIN Project p
ON a.Project = p.ProjectName)
WHERE CStr(ProjectId) +Cstr(Hours) 
      IN (SELECT CStr(ProjectId) + CStr(Amount) FROM ActualHours)

However, when I add the following, an error throws that says invalid use of null:

SELECT ProjectId, d.Code,WeekStart,Hours,NOW() AS RecordDate
FROM ((tmpJacobsActualsByDisciplinekynw a
LEFT JOIN Discipline d
ON a.Discipline = d.DisciplineName)
LEFT JOIN Project p
ON a.Project = p.ProjectName)
WHERE CStr(ProjectId) +Cstr(Hours) +CStr(Nz(d.Code,''))
      IN (SELECT CStr(ProjectId) + CStr(Amount) +CStr(Nz(DisciplineCode,''))  FROM ActualHours)

The query is fixed when I do this:

SELECT ProjectId, d.Code,WeekStart,Hours,NOW() AS RecordDate
FROM ((tmpJacobsActualsByDisciplinekynw a
LEFT JOIN Discipline d
ON a.Discipline = d.DisciplineName)
LEFT JOIN Project p
ON a.Project = p.ProjectName)
WHERE CStr(Nz(ProjectId,'')) +Cstr(Nz(Hours,'')) +CStr(Nz(d.Code,''))
      IN (SELECT CStr(Nz(ProjectId,'')) + CStr(Nz(Amount,'')) +CStr(Nz(DisciplineCode,''))  FROM ActualHours)

My question is..why is this? Why does the query work the first time without the Nz and not work the second time when I have Nz only on d.Code. Why do I have to put Nz on everything?

There is no null values that I see exist in the tables for the records.

Thank you!

Kind Regards,

 

[Micron]

I may be off base here, but I think this is because of a combination of things.
1) I'm thinking there is a NULL value in at least one record which you haven't noticed, and
2) You appear to be using an arithmetic operator to add values together rather than concatenating them. If one of the values is a string, I believe Access will interpret that they cannot be added, so they are concatenated thus no noticeable difference to you. However, in arithmetic operations, if a result of part of an expression is NULL, the whole operation evaluates to NULL, thus the "invalid use of NULL" in your operation. At least I believe so.

Try creating a query bringing in those 3 fields from [Actual Hours] and in each field, specify NULL as the criteria, but each will have to be on its own row in query design so that the sql is looking for something like this: SELECT ProjectId, Amount, DisciplineCode FROM ActualHours WHERE ProjectId is NULL OR Amount is NULL OR DisciplineCode is NULL;
and see if you get any records where one or more values are NULL in one or more rows.

I'm curious - are you attempting to add or string together the values or is there some other reason for using the + operator?