Trying to Convert Excel nested IF to Access nested IIF

Hi, I'm trying to transfer the formula below from excel into access. I've tried several different variations in access the last attempt is at the bottom. Am I using the AND function correctly? Thank you for your help.


Excel
=IF(AND(D2=0,H2="Y"),"Stop Sale",IF(AND(F2>=0,H2="Y"),"Cut-off",IF(AND(F2>0,H2<>"Y"),"Available",IF(AND(F2=0,H2<>"y"),"Sold Out"))))

Access
IF2: IIF(AND([Allot]=0,[SS]="Y"),"Stop Sale",IIF(AND(F2>=0,[SS]="Y"),"Cut-off",IIF(AND([Rem]>0,[SS]<>"Y"),"Available",IIF(AND([Rem]=0,[SS]<>"y"),"Sold Out"))))

Do you need to change the F2 to [Rem] in the second IIF ?

Hope this helps.

Pete

I did miss that thank you, unfortunately that formula did not work...

Originally Posted by Pete_UK

Do you need to change the F2 to [Rem] in the second IIF ?

Hope this helps.

Pete

Originally Posted by alansidman

The Access "And" function does not operate in the same manner as Excel.

IIF(Field1 = Field2 and Field3 = Field4,True, False)

Look at this link also

https://www.mrexcel.com/forum/micros...nt-access.html

I tried based on the link but still no luck. Here's where I'm at any thoughts?

if2: IIf(([Allot]=0) And ([SS]="Y"),"Stop Sale",IIf(([Rem]=0 And [SS]="Y"),"Cut-off",IIf(([Rem]>0) And ([SS]<>"Y"),"Available",IIf(([Rem])=0 And ([SS]<>"Y"),"Sold Out"))))