Switch function in Access


Today i came across Switch function for a forum question. The user wants to sort his Month column in right order instead of alphabetically. It would be easy to do this in SQL Server with CASE statement. Thought IIF in Access should work, but it stops at nested level 10. Here comes the Switch function, the syntax of Switch is:
Switch(Expression1, What To Do If Expression1 Is True,
       Expression2, What To Do If Expression2 Is True,
       Expression_n, What To Do If Expression_n Is True) As SomeValue

Unlike IIf(), the Switch() function does not take a fixed number of arguments. It takes as many combinations of <Expression -> Statement>s as you need. Each expression is evaluated. If the expression evaluates to true, the statement that follows it executes.

http://www.yevol.com/en/access2007/Lesson13.htm

The solution for the user is:
Query:

SELECT * FROM (SELECT id, TalkMonth, CINT(Switch([TalkMonth]='January',1,

[TalkMonth]='February',2,

[TalkMonth]='March',3,

[TalkMonth]='April',4,

[TalkMonth]='May',5,

[TalkMonth]='June',6,

[TalkMonth]='July',7,

[TalkMonth]='August',8,

[TalkMonth]='September',9,

[TalkMonth]='Octeber',10,

[TalkMonth]='November',11,

[TalkMonth]='December',12,

[TalkMonth] is NULL,999)) As TalkMonthNumber

FROM 12Months)

ORDER BY TalkMonthNumber


 And use the new numeric column in SortExpression for the string column.

<asp:BoundField DataField="TalkMONTH" HeaderText="TalkMONTH" SortExpression="TalkMonthNumber" />

However,it seems that Switch can only go upto level 14 for nesting.
 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s