[AccessD] Re: A2K: Testing for NULLS in a Query

Darren,

I would do it in code and not in queries.

The first one would be a recordset for each student.

SELECT StudentID FROM tblStudent:
Go to the first record
loop until rs1.eof
check in a second recordset for the Assessment for that StudentID
SELECT * FROM tblAssessment WHERE StudentID = rs1!StudentID
if rs2.bof and rs2.eof   ' no records found
         then append the record
end if
move to the next record of rs1
loop back up

there it is in pseudo-code.

Robert

At 02:52 AM 11/6/2003 -0600, you wrote:
>Date: Thu, 6 Nov 2003 16:00:10 +1100
>From: "Darren DICK" <d.dick at uws.edu.au>
>Subject: [AccessD] A2K: Testing for NULLS in a Query
>To: "AccessD List" <AccessD at databaseadvisors.com>
>Message-ID: <00f101c3a422$dba47f60$3c619a89 at DDICK>
>Content-Type: text/plain;       charset="iso-8859-1"
>
>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