iif or case

[bhc92]

I have a table (CustomValues) which has a field called content which will contain the follwing values
Severity Critical, Severity High, Severity Medium, Severity Low; I need to populate a new colum (SLA) as follows

if customvalues = severity Critical then SLA = 1
if customvalues = severity High then SLA = 3
if customvalues = severity Medium then SLA = 30
if customvalues = severity Low then SLA = 60

Its has been a long time since I did any access, I have tried to do a nested query but realised values would be overwritten, I thought about using CASE; I wondered if anybody knew of a quicker way round this??

Cheers.

[hariharanmca]

Yes you can use iif in ms access like

select iif(fieldValue = 0, "Zero","One") as fldValue from tblTableName

[bhc92]

Could you expand a little for me please, as I say its been a long time.

[bhc92]

I have tried this but I am short a bracket or two somewhere I think

sla: IIf(([content])="Severity 1 (Critical)",1,IIf(([content])="Severity 2 (High)",3,IIf(([content])="Severity 3 (Medium)",30,60)

[hariharanmca]

I think this will help you

SELECT 
iif(customvalues = 'severity Critical', 1,
iif(customvalues = 'severity High',3,
iif(customvalues = 'severity Medium',30,60))) AS SLA 
FROM tblTableName

In ms access you cannot use case.
you can use case in sql setver 2000.

Can you explain why you are using like this (Storing numbers is better).