# Trying to Convert Excel nested IF to Access nested IIF

1. ## 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"))))

2. ## Re: Trying to Convert Excel nested IF to Access nested IIF

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

Hope this helps.

Pete

3. ## Re: Trying to Convert Excel nested IF to Access nested IIF

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

4. ## Re: Trying to Convert Excel nested IF to Access nested IIF

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

5. ## Re: Trying to Convert Excel nested IF to Access nested IIF

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"))))

6. ## Re: Trying to Convert Excel nested IF to Access nested IIF

Is this in a query? Are all fields in the same table? If not, then you will need to identify the table name and the field name, ie. [Table1.Field1]=Something

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1