[AccessD] A2K: Testing for NULLS in a Query

Darren,
Have you tried
IsNull([tblResults]![AssesmentID]) OR IsNull([tblResults]![StudentID]) 

Or move the second criteria to the second row of the query grid

Rgds
Robin Lawrence

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren DICK
Sent: 06 November 2003 05:00
To: AccessD List
Subject: [AccessD] A2K: Testing for NULLS in a Query


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




_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com