Please help with sort by CASE

1

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.

2

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.

3

Rogier, Thank you so much, this worked!

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

Dave

4

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

5

Thanks again.