[AccessD] A2K: Testing for NULLS in a Query

Darren DICK d.dick at uws.edu.au
Wed Nov 5 23:00:10 CST 2003


Hello all
I have a Table that holds a swag of various ID's called tblResults
Many fields but of interest here is...
StudentID, AwardID, CourseID, SubjectID, AssessmentID all Numbers (Long Int)

I perform an append query if the one of the ID's is missing 
(using IS NULL in the criteria as per the find Unmatched Query Wizard)

So I have a student (StudentID = 2 ) who does as assessment (AssessmentID = 55)

Quite simply if there is no occurence of the Assessment ID 55 (ie Is null in the Criteria of the Append Query)
Excellent then the Append query sparks up and all is well.

Small snag though. Many students may sit assessment no 55.  So there may be no occurence of AssessmentID 55
for student no 2 but there may be an occurence of it for say...Student Number 1 or others

As a result the Is Null Criteria is false and doesn't spark the append query Because there IS an assessment 55 in the 
Results Table only it belongs to Student no 1 not student no 2

How do I test for Null in 2 fields in order to spark up the append query.
IE there may be an AssesmentID of 55 in the table but for Student ID 1 
So...
How do I test for Is Null Assessment ID 55 AND isNull student ID2
I have discovered it is not as simple as putting Is Null in the Critera for Assessment ID and Is Null for Criteria For Student ID
I have also tried  IsNull([tblResults]![AssesmentID]) And IsNull([tblResults]![StudentID]) no joy

Many thanks in advance


Darren






More information about the AccessD mailing list