#1
  1. Mmmm...Donkey punch...
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2004
    Location
    All up in your grill
    Posts
    981
    Rep Power
    43

    Using a CASE statement in MS Access


    (I wish there were an MS Access forum here!)

    Anyway, I'm trying to run the following query in Access 2003, but it keeps telling me there's a syntax error: missing operator. Can someone decipher?

    SELECT
    CASE when
    A.FACILITY = "This" and B.CT_INS_TYPE = "Commercial" then count(B.account_ID) else "None" end as Count_of_ID
    FROM Table1 A INNER JOIN Table2 B ON Table1.ACCOUNT_ID = Table2.ACCOUNT_ID
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    857
    Rep Power
    388

    Cool



    You may need to use the IIF() function instead:

    Code:
    SELECT
      SUM(IIF(A.FACILITY = "This"
            and B.CT_INS_TYPE = "Commercial", 1, 0)) as Count_of_ID
      FROM Table1 A INNER JOIN Table2 B
          ON Table1.ACCOUNT_ID = Table2.ACCOUNT_ID


    Comments on this post

    • Cheesefood agrees : Thank you.
  4. #3
  5. Mmmm...Donkey punch...
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2004
    Location
    All up in your grill
    Posts
    981
    Rep Power
    43
    Originally Posted by LKBrwn_DBA

    You may need to use the IIF() function instead:

    Code:
    SELECT
      SUM(IIF(A.FACILITY = "This"
            and B.CT_INS_TYPE = "Commercial", 1, 0)) as Count_of_ID
      FROM Table1 A INNER JOIN Table2 B
          ON Table1.ACCOUNT_ID = Table2.ACCOUNT_ID


    That's what I had to do. Thanks!
  6. #4
  7. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    Originally Posted by Cheesefood
    (I wish there were an MS Access forum here!)
    btw there is one on sister site aspfree here FYI
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    Originally Posted by Cheesefood
    (I wish there were an MS Access forum here!)
    you're in it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    857
    Rep Power
    388

    Cool



    Also, you may want to consider changing your database language from Access SQL to SQL Server compatible TSQL which has many more options and features.

    Just click on: Tools > Options > Tables/Queries > SQL Server compatible SQL

  12. #7
  13. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    Consider posting in the default font instead of straining our eyes. ;-)
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  14. #8
  15. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    857
    Rep Power
    388

    Thumbs down


    Originally Posted by medialint
    Consider posting in the default font instead of straining our eyes. ;-)
    Why? I like it like this.

  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    Originally Posted by LKBrwn_DBA
    Why? I like it like this.

    but nobody else does

    this isn't the first time you've been asked to change it, either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    857
    Rep Power
    388

    Thumbs up


    Originally Posted by r937
    but nobody else does

    this isn't the first time you've been asked to change it, either
    And what? -- It definitely looks better than the default font.

    Comments on this post

    • pabloj disagrees : Back to red ...
    • Sepodati agrees : I don't think so, but you can't really bitch when people use the features the site's given them... change fonts till your heart's content. :)
    Last edited by LKBrwn_DBA; January 26th, 2007 at 01:34 PM.

IMN logo majestic logo threadwatch logo seochat tools logo