By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,829 Members | 1,828 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,829 IT Pros & Developers. It's quick & easy.

Using switch instead of case to specify order

P: n/a
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...
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
to**@ascnet.org (Tom Morgan) wrote in
news:78**************************@posting.google.c om:
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
Nov 12 '05 #2

P: n/a
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.org> wrote in message
news:78**************************@posting.google.c om...
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...

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.