IIfNull Not Working, but =0 does?

[marylynn]

Why does the first statement yield correct results, but the IIf(IsNull) statement does not? (It places the same value in both columns, subtracting the PickupRequired Fee for both True and False conditions, whether PickupRequired box is checked or unchecked.)
This works: NetDonation: IIf([PickupRequired]=0,[DonationValue],[DonationValue]-8.75)
This doesn’t work: NetDonation: IIf(IsNull([PickupRequired]),[DonationValue],[DonationValue]-8.75)
Can someone please tell me what's wrong?
Thank you much.

 

[pr2-eugin]

If the Type of the Field [PickUpRequired] is Yes/No then you have to either use -1/True or 0/False to test it.. As it CANNOT have a Null value.. It is either True or False.. There is no third state..

 

[marylynn]

Thank you for your prompt response.

Would there be another way to properly use the IIf(IsNull) statement for this query? Can I change the TYPE of the PickupRequired field (which is a checkbox) to another TYPE other than Yes/No? (The concept I am trying to learn is how to use the IIf(IsNull) statement.)

Many thanks.

 

[pr2-eugin]

Well the only question that we need to ask here is why do you need it to be another type when this works perfectly??

If you really need to change the type to something (WHICH I STRONGLY DISAGREE), you can do so..

Also a Zero Length String(ZLS) is not a Null value.. they are quiet different.. If you have allowed ZLS in the field to be Yes then your IsNull() check will fail as Null <> ZLS.. So think carefully before you choose to do it..

 

[marylynn]

I guess the fact that there is a checkbox in the field means there is "something" in the field?

I was able to use IIf(IsNull) with the Company field when the field was completely blank because the record was for an individual who did not have a company.

So I will stick with the =0 for the PickupRequired checkbox field since it works fine and is the simple solution.

Thank you very much.

 

[jdraw]

Some more fuel for the fire

see http://allenbrowne.com/vba-NothingEmpty.html

 

[MSAccessRookie]

I guess the fact that there is a checkbox in the field means there is "something" in the field?

I was able to use IIf(IsNull) with the Company field when the field was completely blank because the record was for an individual who did not have a company.

So I will stick with the =0 for the PickupRequired checkbox field since it works fine and is the simple solution.

Thank you very much.

Have you tried using nz() instead of IsNull()? Sometimes that can make a big difference. The format is nz(YourValue, ValueifNull), and the original value will be retained unless it is Null.

-- Rookie

 

[marylynn]

Thank you all for your helpful advice and instruction. I will retain the tips from jdraw for future reference also as I continue to learn.
I could not get the right result with Nz, (syntax issues, etc.) so I'll stick with IIf =0 for now.

Thanks everyone.

 

[Brianwarnock]

Since you are sensible enough to want to learn may I quote Pat Hartman This is from another discussion on the use of isnull .

To answer your other question IsNull() is a VBA function but "Is Null" is SQL syntax. It probably doesn't matter in this case but I don't use VBA functions when SQL has the necessary language element. There is no reason to force the SQL engine to invoke the VBA library unnecessarily.

Brian