Multiple IIF in Query

[BNPMike]

I'm trying to code a multiple IF statement to assign marks to a test. I'm sure I've done this before but I keep getting an error:
An expression you entered contains invalid syntax

The code (in the QBE grid) is:

Mk: IIf(([Answer]='+' And [Response]='++'),5,( IIf(([Answer]='+' And [Response]='+'),4,-9)))

Can you see what is wrong?

 

[r937]

Mk: IIf([Answer]='+',IIf([Response]='++',5,IIf([Response]='+',4,-9),-9))

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[BNPMike]

I've pasted that in and it gives me the same error message - weird of what?

 

[r937]

my bad, try this --

IIf([Answer]='+',IIf([Response]='++',5,IIf([Response]='+',4,-9)[red]),-9)[/red]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[BNPMike]

I've just spotted what's wrong - my original one now works.

I'd written the expression in Word because it has nine Ifs altogether and the Access editor won't let you lay your source out nicely to check all your brackets balance out. I probably pasted in the expression with carriage returns. That didn't work so I removed the returns and surplus space, and they seem to disappear. I then kept pasting in shorter and shorter expressions to see what would work but I think some bits of the previous code were left but not visible. I deleted the query and started afresh and it worked OK.

Maybe it's better to edit the SQL, at least you can see what you're doing.

 

[dhookom]

BNPMike,
Have you consider modeling this in tables rather than nine IIfs? In your original expression, I count seven hard-coded values. I generally assume these values will change over time. I would hate to maintain a query that has such complex business rules. Consider Data belongs in your tables -- not in your code

At the very least, I would create a small user-defined function that would accept the field values and return the appropriate Mk. A function would provide a single place for you to maintain business calculations. You could also add comments and it would be much easier to maintain [red]when[/red] you calculations change.

Duane
Hook'D on Access
MS Access MVP

 

[BNPMike]

The actual application is a psychometric test. This is marking each answer. The 'correct' answer and the subject actual answer are both values in a table. The query calculates scores and then I sum those by personality dimension (at least I hope that's how it will work - it assumes each question is only used by one personality dimension).

Whilst the marking rule is liable to stable over very long periods as they are expensively normalised tests, I would not normally do it this way if it were going to be used in a live production context.