Handling Null and 0

[Bob4grave]

I have a query 3_qryMakeFinishMaster that joins a query of all competitive place results (qryJoinFinishers) with a table that assigns a Point value for each Place (tblPlacePoints).

The problem is people who didn't finish, who I want to get Points=0. They show up in qryJoinFinishers with a null value for Place, and even though there is a matching null value in tblPlacePoints with a Points score of zero, the result is somehow a null value for Points in 3_qryMakeFinishMaster.

I have even tried forcing the issue with an IsNull criteria statement in 3_qryMakeFinishMaster where Points are assigned:
IIf(IsNull([qryJoinFinishers].[Place]),"0",[tblPlacePoints].[Points])
But all it does is drop all participants with null Places from the results set.

What am I missing?

 

[Isskint]

There are 3 values that cause issues;
Zero, Null and "" - these are NOT the same. Try using the NZ() function to test for null and return a value you can use OR add "" to the value and test for "".

EG
Nz([qryJoinFinishers].[Place]),0) would return a value of 0 for Null
OR
If [qryJoinFinishers].[Place] & "" = "" Then

 

[Bob4grave]

This is useful and relevant info. Thanks. But it still doens't solve the issue, as I'm getting an empty result set, and I'm wondering if I've combined it with a more fundamental mismatch in the same query.

I'm using:
Nz(([qryJoinFinishers].[Place]),0)
as a criteria in
Field: Points
Table: tblPlacePoints
In other words, I'm checking for non-zero in a subquery from another table. This is the part of the main query where Points are assigned in the master query. Is that correct, or is there a logic error there?

 

[Isskint]

So your query should return records where;

tblPlacePoints.Points = Nz(([qryJoinFinishers].[Place]),0)?
So long as these are both numeric values (and values are recorded under similar guidlines - no point looking for values = 5000 if the criteria field only has values <10 say) this should work.

It may be useful if you could post any related query SQL and their relationship(s) to each other.

 

[Bob4grave]

Hmm. Same result. Diving into the gritty details, this is a master query that does lookups across five tables for various items, with the following SQL:

SELECT qryJoinFinishers.MeetClass, qryJoinFinishers.MeetCode, qryJoinFinishers.ClassCode, qryJoinFinishers.WholeName, qryJoinFinishers.Place, tblPlacePoints.Points, qryJoinFinishers.Time, Registrations.SchoolCode, qryJoinFinishers.FinishID, tblClassCodes.Course, tblClassCodes.CourseName, tblClassCodes.Class, tblSchoolCodes.School, tblSeasonSchedule.MeetName, tblSeasonSchedule.MeetDate, tblSeasonSchedule.VenueName
FROM ((((qryJoinFinishers LEFT JOIN tblPlacePoints ON qryJoinFinishers.Place = tblPlacePoints.Place) INNER JOIN Registrations ON qryJoinFinishers.WholeName = Registrations.WholeName) INNER JOIN tblClassCodes ON qryJoinFinishers.ClassCode = tblClassCodes.ClassCode) INNER JOIN tblSchoolCodes ON Registrations.SchoolCode = tblSchoolCodes.SchoolCode) INNER JOIN tblSeasonSchedule ON qryJoinFinishers.MeetCode = tblSeasonSchedule.MeetCode
WHERE (((tblPlacePoints.Points)=Nz(([qryJoinFinishers].[Place]),0)))
ORDER BY qryJoinFinishers.MeetClass, qryJoinFinishers.Place;

Of all the table lookups, tblPlacePoints is the one to focus on. As written above, the query returns no records. With the WHERE clause removed, it returns all the records it should, but nonfinishers have null for Points when I want them to have 0.

 

[Bob4grave]

Interesting new development... after a visit to http://www.techonthenet.com/access/functions/advanced/nz.php, I tried using the basis of the nz logic as a new field and it worked:
Nz(([tblFinishMaster].[Points]),0) AS Newpts

Does this mean that nz can be used only at the field level and not as a criteria?

 

[Isskint]

OK so lets look at the WHERE, there are a couple of things that jump out at me.

1. Your SQL at the moment says;
   1. Show me data where Points = Place. To a layman that sounds ..wrong. Points are awarded for doing something and Place is acheived by an accumulation of points. EG in football, you win 10 games in a row and get 30 points (3*10) and that puts you in Place 1. So your criteria Points = Place (30 = 1 OR 3=1) would never be met.
2. You said in your opening post about Points being Null or blank so you would need to check that with NZ() also
   1. WHERE (((NZ(tblPlacePoints.Points,0))=Nz(([qryJoinFinishers].[Place]),0)))

One other thought, you could always use a little VBA to check Place = Null or unfinished etc at the time of input and set the Place and Points to zero so you do not have this issue.

 

[Bob4grave]

Ah. A bit of context here. These are orienteering races, with competitors finishing in a certain order (Place) and points awarded accordingly (100 for 1st place, 95 for 2nd, etc). So I think the logic concern in your item #1 is met.

I finally solved the issue by moving the nz logic into its own field:
Nz(([tblPlacePoints].[Points]),0) AS Points

Thanks for the tip about going to nz, Isskint. Ultimately, it got the job done.