Elizabeth Carter
Elizabeth.Carter at telus.com
Thu Nov 6 13:20:06 CST 2003
Hi Darren, You could try doing a select query for you student ID assessment ID combination and if no rows are returned run the append query. Liz -----Original Message----- From: Robin Lawrence [mailto:rgilimited at btconnect.com] Sent: November 6, 2003 12:52 AM To: 'Access Developers discussion and problem solving' Subject: RE: [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 _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com