IIF Statement in a Query - Null Value not calculated

[Tamrak]

Good morning,

I have a query that seems to work fine, except it won't calculate the null value.

There are only three fields involved.

MemberType: (Text, either "Social Member" or "Full Member")
ChargeTotal: (Number, currency, 2 decimals)
Due: This is where I would like to write a query, which is appeared below. (The field type is Number, Currency, 2 decimals.)

IIf([MemberType]="Social Member" And [ChargeTotal]<35,35,IIf([MemberType]="Full Member" And [ChargeTotal]<100,100,0))

Calculation works fine except:

1. When the field [ChargeTotal] is Null, in the situation that a particular customer does not charge anything, the due is blank. Even though I stated that if the ChargeTotal is less than 35, return me 35 or less than 100, return me 100.

For "Social Member", it supposes to have 35 recorded for the due and for "Full Member", it supposes to have 100 recorded for the due.

Can anyone assist me in modifying this query code, so, it will work on all logics given? I believe that I might be missing something.

Should I use a NZ function at the ChargeTotal? Let me know. Thanks again.

 

[traingamer]

Access doesn't use the null value as you might expect. Try converting nulls to zeroes with Nz() like

IIf([MemberType]="Social Member" And Nz([ChargeTotal],0)<35,35,IIf([MemberType]="Full Member" And Nz([ChargeTotal],0)<100,100,0))

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Tamrak]

Traingamer,

Thank you for your prompt assistant. Everything is working fine now. I believe that I should use the NZ function somewhere but I could not think where it should go.

Thanks.