+ Reply to Thread
Results 1 to 6 of 6

Trying to Convert Excel nested IF to Access nested IIF

  1. #1
    Registered User
    Join Date
    08-01-2017
    Location
    Florida
    MS-Off Ver
    2013
    Posts
    47

    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. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    16,069

    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. #3
    Registered User
    Join Date
    08-01-2017
    Location
    Florida
    MS-Off Ver
    2013
    Posts
    47

    Re: Trying to Convert Excel nested IF to Access nested IIF

    I did miss that thank you, unfortunately that formula did not work...
    Quote Originally Posted by Pete_UK View Post
    Do you need to change the F2 to [Rem] in the second IIF ?

    Hope this helps.

    Pete

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2016
    Posts
    13,766

    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. #5
    Registered User
    Join Date
    08-01-2017
    Location
    Florida
    MS-Off Ver
    2013
    Posts
    47

    Re: Trying to Convert Excel nested IF to Access nested IIF

    Quote Originally Posted by alansidman View Post
    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. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2016
    Posts
    13,766

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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