Alernative to SWITCH function???

[elender]

I am building a query for survey data and I want to assign a numeric value to the answers of the survey questions.
The key is that the same answers indicate different amounts of "points" depending on the question. In other words, Yes might =1 sometimes but not others.



For example:
Question 1: Are you satisfied with your life? Yes= 1pt, More or Less=1pt, No=0 pts
Question 2: Do you feel full of energy? No=1 pt, More or Less=0pts Yes= 0 pts

I wrote in the SWITCH function originally (example below)
SWITCH([Q1]=Yes,1,[Q1]="More or Less",1,[Q1]=No,0)

But alas, it is working for some columns and not others. In expression column I wrote in the proper point system for the corresponding question, but still no luck. I also tried the IIF function which also didn't work.

Is there an alternative way to accomplish this? Is my expression incorrect?

[aytee111]

Store them on the table with the questions, 3 separate points fields, then select the one corresponding to the answer.

[ranman256]

1. a lookup table to join in the query to exchange results
2, a Case statment in a custom function
3, IIF statment in the query

[elender]

1. a lookup table to join in the query to exchange results
2, a Case statment in a custom function
3, IIF statment in the query

Hi,
Can you elaborate on #3? I am fairly new to Access, but below is the IIF statement I tried and it did not return any results:

=IIf([LS1]="Yes",1,IIf[LS1]="No",0)

[ranman256]

=IIf([LS1]="Yes",1,0)

[CJ_London]

your code does not make sense

SWITCH([Q1]=Yes,1,[Q1]="More or Less",1,[Q1]=No,0)

([Q1]=Yes and [Q1]=No implies Q1 is a Boolean field (i.e. a yes/no field)

[Q1]="More or Less" implies Q1 is a text field

it can't be both

[LS1]="Yes" and [LS1]="No" implies LS1 is text