Writing an IIF Access statement with an embedded IsNull statement

[Promise007]

I am in the process of trying to build an expression in Access 2016. I have three tables that are joined by a common field in a query. The first table is called No Math and it has a field named No Math Test. There is also a table called No Reading with a field named No Reading Test. The third table is called No MAP Test and has a field named Completed. I am a teacher and I am trying to create a way to keep track of who has taken the Math and Reading MAP tests. Some students have taken one of the two tests or neither or both tests. I have created a query called Missing MAP Scores that links each table by the student's ID number. In the query I have the field No Reading Test next to the field No Math Test and the last field is the Completed field. Since every student has not taken every test. I am trying to show only those records that have a value under No Reading Test and NO Math Test. I have tried to code the expression so that it puts a value of "Yes" in the Completed field if the other two fields are empty. This will allow me to sort for "No", which means the students has not completed the two tests, or not display the record at all if the student's record is blank for fields No Math Test and No Reading Test. The values for both fields are Math or blank and Reading or blank. This is the expression that I started with: IIf(IsNull([No Math]![No Math Test] And [No Reading]![No Reading Test], Yes, No)

It is not working. Apparently, I am doing something wrong. Any suggestions? Thanks.

[Promise007]

I have learned a few things since the initial post. First, I was trying to put the expression down in the criteria section of field from one of the tables. I have moved the expression to the Field name of a blank column in the Query itself. I played around with it and I got it to work somewhat. This is my expression at this point:

Testing Completed: IIf(IsNull([No Math]![No Math Test] And IsNull([No Reading]![No Reading Test])),Yes,No)

When I run it, it puts a 0 in the columns when either No Math Test or No Reading Test are not blank or when both are not blank. When both are blank it puts a -1 in the column. Unfortunately, I was trying to get Yes and NO and I can not sort on the 0's and -1's. Something is still not quite right.

[PhilOfWalton]

The Yes & No both need to have double quotes round them.

"Yes","No"

Alternatively you can use -1 (True) and 0 (False)

Testing Completed: IIf(IsNull([No Math]![No Math Test] And IsNull([No Reading]![No Reading Test])),"Yes","No")

or

Testing Completed: IIf(IsNull([No Math]!-1,0)

Phil

[Promise007]

PhilOfWalton,

I added the double quotes as you suggested and I got an error message that said I had a syntax error. I changed it to single quotes and I got all No's.

Then I copied and pasted what you had in your message and it worked like a charm. You are a genius! Thank you very much for your help.

[PhilOfWalton]

No Problem. Glad to help

Phil

[Promise007]

Everything is almost complete. I still have one error that will not allow me to sort by the Yes or No. When I try to sort it displays the following error message:
Syntax error (missing operator) in query expression 'Testing Completed'.

What am I missing? Thanks.

[PhilOfWalton]

Ah, looking at it again, I think the bracketing is wrong

Try

Testing Completed: IIf(IsNull([No Math]![No Math Test]) And IsNull([No Reading]![No Reading Test]),"Yes","No")

Incidentally, if I were you, I would avoid spaces in field & table names. You have to put square brackets round them.

Isn't NoMath!NoMathTe st just as easy as [No Math]![No Math Test]

Phil

[Promise007]

Phil,

Unfortunately, it is still not letting me sort. It does what it it supposed to do, putting Yes in the column for those who have completed both tests and No otherwise, but when I go to sort by Yes and No, I get an error.

I appreciate your advice on the spaces, I did not realize that putting a space in the field name would require brackets. I will go back and rename the fields without the spaces and go from there.

Thanks.

[PhilOfWalton]

So how are you trying to do your sort?

Note: Advice against Spaces in table names, form names & Query names as well.

Phil

[Promise007]

Phil,

I tinkered with it and now it works, but I don't see any difference from what we were using a minute ago. This is the expression that works:
Testing Completed: IIf(IsNull([NoMath]![NoMathTest]) And IsNull([NoReading]![NoReadingTest]),"Yes","No")

Thanks much!