+ Reply to Thread
Results 1 to 4 of 4

Help on IF formula for an access database

  1. #1
    Forum Contributor
    Join Date
    06-13-2012
    Location
    ND
    MS-Off Ver
    Excel 2010
    Posts
    152

    Help on IF formula for an access database

    I have an access database that calculates a score for each salesman. All the forumlas I am currently using are below. I need a formula that uses the "open" [status] and gives it a -1 if the open date is 30 greater than today's date. (using valid fr date). Below is all the formulas I currently have. At the end I will add all the points for every salemen to get a total number. Let me know if you need more info.

    Ordered Points: IIf([status]="Ordered",5,0)
    Closed Points: IIf([status]="Closed",1,0)
    Open Points: (formula inquiring about)
    Rejection Reason Points: IIf([Rejection Reason]=" ",0,1)
    Competitor Lost Points: IIf([MFG]=" ",0,1)
    Distributor Lost Points: IIf([DIST]=" ",0,1)




    Attachment 229983

  2. #2
    Forum Contributor
    Join Date
    06-13-2012
    Location
    ND
    MS-Off Ver
    Excel 2010
    Posts
    152

    Re: Help on IF formula for an access database

    Rating: iIF(and([004 Employee Number on Quote by material]![status] ="open", or([006 Total Quotes greater than 30 days]![Total Quotes over 30 Days] > 30)), -1, 0))

    This is what I have so far, but it isn't working. If anyone could help that would be appreciated. Thank you.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Help on IF formula for an access database

    Try this: IIF([004 Employee Number On Quote By Material].status="open" and [006 Total Qutoes greater than 30 days].[Total Quotes over 30 days]>30,-1,0)

    Your syntax was an excel syntax. Access requires that you spell out each condition. There is no "and" function.

    Look at this and particularly the last example. http://www.techonthenet.com/access/f...vanced/iif.php
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Forum Contributor
    Join Date
    06-13-2012
    Location
    ND
    MS-Off Ver
    Excel 2010
    Posts
    152

    Re: Help on IF formula for an access database

    Thank you. I was close, but not close enough Much appreciated!

+ 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