Using switch instead of case to specify order

[Tom Morgan]

Hi everyone, I'm having a brain freeze today so if the answer is a
misplacced comma or a missing command, I would be happy.

What I want to do is use access to return results via an .asp page.

<coff> is the table
<postion> is the column
I do not want the results in alpha order, but the order specified
below, but I keep getting a syntax error: 'Syntax error (missing
operator) in query expression '

Here is the massive statement that doesn't want to fly. ;-/

SELECT * FROM coff WHERE (CHNAME = 'nwoc')

Switch([Position]="President" ,1,[Position]="Vice
President",2,[Position]="Secretary" ,3,[position]="Treasurer" ,4,[postion]="Secretary
/ Treasurer", 5)

Thanks in advance...

[David W. Fenton]

to**@ascnet.org (Tom Morgan) wrote in
news:78******** *************** ***@posting.goo gle.com:

Hi everyone, I'm having a brain freeze today so if the answer is a
misplacced comma or a missing command, I would be happy.

What I want to do is use access to return results via an .asp
page.

<coff> is the table
<postion> is the column
I do not want the results in alpha order, but the order specified
below, but I keep getting a syntax error: 'Syntax error (missing
operator) in query expression '

Here is the massive statement that doesn't want to fly. ;-/

SELECT * FROM coff WHERE (CHNAME = 'nwoc')

Switch([Position]="President" ,1,[Position]="Vice
President",2,[Position]="Secretary" ,3,[position]="Treasurer" ,4, [pos tion]="Secretary / Treasurer", 5)

Er, where are you using the Switch() statement? Does this work:

SELECT coff.*, Switch([Position]="President" ,1,[Position]="Vice
President",2,[Position]="Secretary" ,3,[position]="Treasurer" ,4,
[position]="Secretary / Treasurer", 5) As PositionCode
FROM coff
WHERE (coff.CHNAME = 'nwoc')

Or, you might use a lookup table that maps the positions to the code
number.

Or, if there's a 1:1 relationship between position and code number,
then store only the number and have the position stored in the
lookup table.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[DFS]

Tom,

Your Switch seems to be disconnected from your SELECT statement. Try:

SELECT *,
Switch([Position]="President" ,1,
[Position]="Vice President",2,
[Position]="Secretary" ,3,
[Position]="Treasurer" ,4,
[Position]="Secretary / Treasurer", 5) AS JobCode
FROM coff
WHERE CHNAME = 'nwoc'
ORDER BY
Switch([Position]="President" ,1,
[Position]="Vice President",2,
[Position]="Secretary" ,3,
[Position]="Treasurer" ,4,
[Position]="Secretary / Treasurer", 5)

"Tom Morgan" <to**@ascnet.or g> wrote in message
news:78******** *************** ***@posting.goo gle.com...

Hi everyone, I'm having a brain freeze today so if the answer is a
misplacced comma or a missing command, I would be happy.

What I want to do is use access to return results via an .asp page.

<coff> is the table
<postion> is the column
I do not want the results in alpha order, but the order specified
below, but I keep getting a syntax error: 'Syntax error (missing
operator) in query expression '

Here is the massive statement that doesn't want to fly. ;-/

SELECT * FROM coff WHERE (CHNAME = 'nwoc')

Switch([Position]="President" ,1,[Position]="Vice
President",2,[Position]="Secretary" ,3,[position]="Treasurer" ,4,[postion]="Se
cretary / Treasurer", 5)

Thanks in advance...