Please help with sort by CASE

I am getting an error when using:

"select name,cut2,dectime,evnum,order,tempsort,pos,next from hstable where val(evnum)=" & EVV & " order by case cut2 when 'F' then 1 when 'S' then 2 when 'P' then 3 else 4 end asc,next, val(dectime)"

The error on the CASE clause is MISSING OPERATOR.

Thanks for any suggestions.

I was wondering what the function VAL does but Google told me that it was MS ACCESS. My knowledge is little about MS ACCESS but it should like something like this:

select name,cut2,dectime,evnum,order,tempsort,pos,next
from hstable where val(evnum)=" & EVV & "
order by
Nz(Switch(
cut2 = 'F', 1,
cut2 = 'S', 2,
cut2 = 'P', 3,
),4) ASC,next, val(dectime)

I use the Nz function to replace NULL and the function Switch. Hopes it helps.

Rogier, Thank you so much, this worked!

My only question now is, Why did CASE not work?

Dave

Because MS Access cannot handle a case, it is not part of his sql engine.

Thanks again.