SQL to Access

Hey Everyone, I am in the process of converting SQL view statements into an Access database and am running into some problems. Basically, my problem is that Access does not seem to have the Case statement that I am use to. What I want to do is look at one field, if that value equals something, return a string, else if the length of a different field satisfies the length requirement, then take that field, else if a different field is a certain length, return a string, else return the value from a different field. In SQL form it looks like this:

Case
when field1 = 'xxxx' then 'text'
when len(field2) = x then field2
when len(field2) = 0 then 'text'
else field3
end as new_field_name

Does anyone have any suggestions on how I can achieve this? Thanks in advance.

re: SQL to Access

You can use a nested IIF(condition,true,false) command like:

select IIF(field1='xxxx','text',IIF(len(field2)>0,field2, 0) ) as Result, ...

The " = x" does startle me, but could also be added in another nesting when replace with a number.

Nic;o)

re: SQL to Access

Thanks for the reply. That is exactly what I ended up doing!