Nested IIf And Or not working

[Bbeards]

Hi, I created this field to determine the status of inventory but it appears that the <> is not working properly when I check the values. Do I have some logic wrong or am I missing something? Thank you for your help.


Inventory Status: IIf([Allotment]=0 And [Stop Sale Flag]="Y","Stop Sale",IIf([Remaining Allotment]>=0 And [Stop Sale Flag]="Y","Cut-off",IIf([Remaining Allotment]>0 And [Stop Sale Flag]<>"Y","Available",IIf([Remaining Allotment]=0 And [Stop Sale Flag]<>"Y","Sold Out","False"))))

Thanks, Brett

[JoeM]

Where are you doing this calculation (query, table, form, report, etc)?
Is "Allotment" a numeric field?

Can you provide some examples of records that are not working that way you like?
Specifically, what is the value of "Allotment" and "Stop Sale Flag", what is your expected value, and what is it returning?

By the way you should change this:

Inventory Status: IIf([Allotment]=0 And [Stop Sale Flag]="Y","Stop Sale",IIf([Remaining Allotment]>=0 And [Stop Sale Flag]="Y",...

to this...

Inventory Status: IIf([Allotment]=0 And [Stop Sale Flag]="Y","Stop Sale",IIf([Remaining Allotment]>0 And [Stop Sale Flag]="Y",...

Since the first condition is checking for "0" and "Y", there is no reason to check for ">=0" and "Y". It should be ">0" and "Y".
Anything equal to 0 will get caught in the first check.

The way you have written it doesn't hurt anything, but it is unnecessary and can be misleading.

[June7]

Is [Stop Sale Flag] a text type field, not Yes/No type?

Consider:

Inventory Status: Nz(IIf([Stop Sale Flag]="Y", Switch([Allotment]=0,"Stop Sale", [Remaining Allotment]>0,"Cut-off"), Switch([Remaining Allotment]>0,"Available", [Remaining Allotment]=0,"Sold Out")), "False")