+ Reply to Thread
Results 1 to 3 of 3

Help Writing an Iff, And Function

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

    Help Writing an Iff, And Function

    Hi,

    I am trying to build the function below into MS Access but I am having trouble writing it.


    These are the what the columns reference.
    D=Allotment H=Stop Sale Flag F=Remaining Allotment

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



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


    Thank you,

    Brett

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Help Writing an Iff, And Function

    What's the problem you're having?
    In VBA with combinations like this I would not use IIF if, elseif
    You write UDF (User Defined Function with Parameters that pass the fieldname and the value but you can also place it in the qery, Access is not my strong thought so it;s just a suggestion
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Contributor
    Join Date
    07-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    104

    Re: Help Writing an Iff, And Function

    In Excel, if something could be empty, Excel ignores it. Access does not. You are possibly getting thrown off by the potential for null values (even if there are no null values). You can set the fields to always be required (a Not Null constraint), or you can modify your query to handle nulls:

    IIF(([Stop Sale Flag] IS NULL) OR ([Allotment] IS NULL) OR ([Remaining Allotment] IS NULL),(what to do if null),IIf(([Stop Sale Flag]="Y") And ([Allotment]=0),"Stop Sale",IIf(([Stop Sale Flag]="Y") And ([Remaining Allotment]>0),"Cut-off",IIf(([Stop Sale Flag]<>"Y") And ([Remaining Allotment]>0),"Available",IIf(([Stop Sale Flag]<>"Y") And ([Remaining Allotment]=0),"Sold Out",0)))))

    Without seeing your database, I am not sure if this solution will fix the problem or not.
    Last edited by SlipEternal; 09-18-2017 at 03:44 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. IF function writing help
    By michje03 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-09-2016, 01:19 AM
  2. [SOLVED] Help writing IF function
    By bigroo1958 in forum Excel General
    Replies: 10
    Last Post: 06-10-2015, 02:22 PM
  3. Replies: 5
    Last Post: 09-15-2014, 12:41 AM
  4. Help with Formula/Function writing
    By Barry83 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-27-2014, 04:26 PM
  5. [SOLVED] Writing a function
    By frisbie17 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-10-2012, 10:35 AM
  6. writing function in VBA
    By johnboy12 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-21-2008, 05:23 PM
  7. Writing a Function
    By svaiskau in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2007, 06:23 PM

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