I am making a grade entering system in Access, and I am running into some issues with a query.

One of my queries gets the class roster of a class. It takes in the students information, and then it also displays a grade next to their name. For the grade entry system, I only want to have students that do not have a grade already entered to appear.

To do this, in my Grade field, I have tried to set the Criteria to Is Null.

This is not working as it just does not display any student at all. The weird thing is, whenever I set it to Is Not Null, it shows me all of the students that have grades entered, and ignores all the students without a grade like I would expect.

So, since the students without grades are left out when the Criteria is set to Is Not Null, that leads me to believe that a student without a grade is null

So, I am very confused why setting the Criteria to Is Null, just leaves me with no results.

I am very new to Access, so if there is some important information I need to provide for adequate help, please let me know!

1 Answers

Albert D. Kallal On

Very likely your join is a standard join. That means ONLY those with a parent record (student will ONLY show). If the child record that contains the grade does not exist, then they don't appear in your query. Fire up the query builder, and double click on the join line - change it to a left join (the option that says to include all students).

Now, when you run the query, records with a grade row will show, and those without will result in the grade column being null. Now, both of your conditions can and should work. It not the condition that is failing but your query not showing any students without a grade regardless of your condition that is the problem.