Two things, before trying to straighten out the hairball that frequently arises out of the use of nested IIF constructs:
First, TYPE is a Reserved Word and shouldn't be used as the name of a Field, as doing so can cause all kinds of problems, perhaps (but not guaranteed) even a part of the problem here.
Secondly, you're using VBA, here, and in VBA the correct syntax for
[Type] Is Null
would be
IsNull([Type])
Likewise
[checkoutDateIn] Is Not Null
would be
Not IsNull([checkoutDateIn])
and so forth.
I'd start off by correcting these missteps and then see what you're left with.
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007