Results 1 to 11 of 11
  1. #1
    gkaro is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    18

    If statement not working

    Hi all,



    new to this so this may seems a little elementary Im sure.
    Trying to figure out how to perform the following

    Table includes
    YEAR, WEEK, ITEM, SALES
    I need a field that gives me something if SALES are less that 50
    I can get it to work with Expr1: IIf([SALES]<50,"UNDER 50",Null)

    but it doesnt work if I have sums on the original table.
    Sometimes I need to delete WEEK and have the total SALES for the whole year as gouped by and SUM.

    The Expr1: IIf([SALES]<50,"UNDER 50",Null) doesnt work on the final calculated number.

    thanks

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    Does this do what you want?

    IIf(Sum([SALES])<50,"UNDER 50",Null)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Try replacing the Null in your second IIf with "". I don't think you can set a value to Null.

    On another note, try to use something other than YEAR and WEEK as your field names. YEAR is a reserved word in Access, and while WEEK isn't, it's still not "proper."

  4. #4
    gkaro is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    18
    It doesnt Work.

    Get message

    Cannot have aggregate function in GROUP BY Clause IIf(Sum([SALES])<50,"UNDER 50",Null)

  5. #5
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by gkaro View Post
    It doesnt Work.

    Get message

    Cannot have aggregate function in GROUP BY Clause IIf(Sum([SALES])<50,"UNDER 50",Null)
    Will you pPost the SQL of your query?

    You might try changing the "Group By" to "Expression".

  6. #6
    gkaro is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    18
    SELECT COSTCO.YEAR, COSTCO.[DYSON SKU], Sum(COSTCO.SALES) AS SumOfSALES, IIf(Sum([SALES])<50,"UNDER 50","") AS Expr1
    FROM COSTCO
    GROUP BY COSTCO.YEAR, COSTCO.[DYSON SKU], IIf(Sum([SALES])<50,"UNDER 50","")
    HAVING (((COSTCO.YEAR)=2012));

  7. #7
    gkaro is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    18
    HEY THANKS..That worked.
    Needed to change Group by to Expression. Don't understand when and whats the difference.
    Any help would be great!

  8. #8
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by gkaro View Post
    HEY THANKS..That worked.
    Needed to change Group by to Expression. Don't understand when and whats the difference.
    Any help would be great!
    Don't know if I can explain it right. Took me a while to (somewhat) understand it.

    Look at this line:

    GROUP BY COSTCO.YEAR, COSTCO.[DYSON SKU], IIf(Sum([SALES])<50,"UNDER 50","")

    "Year" is a field in the query (Bad choice - it is a reserved word).
    "[DYSON SKU]" is a field in the query (spaces are also bad). The query "knows" it can gather the records together by these two fields. But what about "IIf(Sum([SALES])<50,"UNDER 50","")"? It is not a field, it is a formula - aka "Expression". You cannot group by an expression.

    In a Totals Query, if you have an expression (formula/calculation), you have to change "Group By" to "Expression".

    Clear as mud????

  9. #9
    gkaro is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    18
    I guess that makes sense. Have a question about the reserved field [YEAR]. I havent come across any issues but more than one person is telling me this is wrong. Also for spaces.
    I have 3million lines of data and run queries every day. Have not encountered anything wrong.
    Is there something that I am missing. Possibly slower query times? Inaccurate results??
    thanks

  10. #10
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by gkaro View Post
    I guess that makes sense. Have a question about the reserved field [YEAR]. I havent come across any issues but more than one person is telling me this is wrong. Also for spaces.
    I have 3million lines of data and run queries every day. Have not encountered anything wrong.
    Is there something that I am missing. Possibly slower query times? Inaccurate results??
    thanks
    Things like spaces in object names and special characters will cause problems if you ever decide you need better security or you reach Access's size limit and you want to convert to Oracle, MySQL, or SQL Server.

    Reserved words list is found at Allen Browne's site:
    http://allenbrowne.com/AppIssueBadWord.html

    Using reserved words can cause Access to become confused. I had strange problems in an early mdb because I used "Date" as a field name. I had to use brackets to get the code to run. I finally added a prefix, modified the forms, reports, queries and code to eliminate the problem. Now I use a naming convention (stolen form a friend) and no longer have issues due to object names. Other issues, yes, but not because of names.

    Plus, it got confusing because "Date" is not descriptive. "Date" of what???

    My 2 cents....

  11. #11
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    for example, if you write
    SELECT Year from MyTable WHERE myID = 1
    assuming all data exists, the SQL should error out because Access is expecting you to use the Year() function. it also has reserved special characters, *, /, +, etc. To prevent confusion, you would have to place [ ] around every table and field name to force Access to read it as a table or field name. However, that's pretty tedious so we just avoid using the reserved words. Ideal solutions are to either be more descriptive, or use the abbreviated datatype before the name, like dtDate or intID or strName.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. IIF Statement not working...
    By LanieB in forum Queries
    Replies: 6
    Last Post: 01-05-2012, 12:55 PM
  2. Replies: 1
    Last Post: 07-30-2011, 07:58 AM
  3. Delete SQL statement not working properly
    By Alexandre Cote in forum Programming
    Replies: 3
    Last Post: 10-18-2010, 12:56 PM
  4. Textbox IIF statement not working
    By jgelpi16 in forum Forms
    Replies: 2
    Last Post: 08-22-2010, 08:41 PM
  5. Replies: 2
    Last Post: 06-14-2010, 09:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums