SELECT CASE to prevent dividing by zero

Results 1 to 4 of 4

Thread: SELECT CASE to prevent dividing by zero

  1. #1
    Andrew Sinning Guest

    Default SELECT CASE to prevent dividing by zero

    I&#039;m trying to use a SELECT CASE to prevent dividing by zero. <BR><BR>Here&#039;s what I&#039;m trying to avoid: <BR><BR>SELECT *, (100*score/possibleScore) AS percentCorrect FROM ... <BR><BR>The problem occurs when possibleScore = 0! <BR><BR>So, why not: <BR><BR>SELECT *, CASE WHEN (possibleScore = 0) THEN (0 AS percentCorrect) ELSE (100*score/possibleScore AS percentCorrect) END FROM ... <BR><BR>? I&#039;ve tried rephrasing this a dozen time, but I always end up with: Syntax error (missing operator) in query expression. <BR><BR>Thanks.

  2. #2
    Sreedharan Guest

    Default Here you are...

    SELECT *, CASE WHEN (possibleScore = 0) THEN 0 ELSE (100*score/possibleScore ) END AS percentCorrect FROM ... <BR>

  3. #3
    Andrew Sinning Guest

    Default RE: Here you are...(not in Access)

    I&#039;m still getting "Syntax error (missing operator) in query expression." Perhaps CASE doesn&#039;t work with Access databases.

  4. #4
    Join Date
    Dec 1969

    Default Case not supported in Access

    Perhaps yes. You have to use the Access specific iif construct instead.<BR><BR>SELECT *, iif (possibleScore = 0,0,100*score/possibleScore ) AS percentCorrect FROM ... <BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts