MS Access Question

[Spacecadet]

I was doing a little bit of SQL work today as a favour for a contractor friend who wanted some processing doing within an Access SQL query. It involved using a code stored in one field that when applied a date column gives you another date
eg the code 01LL means to go to the last day of the next month, 02LL being last day in 2 months, D030 being date plus 30 days, 0215 being the 15th of 2 months hence etc...

Anyway, I tried using the SWITCH statement and it wouldn't work. But it would work if I removed a lot of the date processing that was going on.
So I split each of the processing parts in to their own columns, and sure enough each one was fine. I put each into its own SWITCH statement and again each was fine as long as there was only one conditional clause in the SWITCH statement.

Now I know that my syntax for the SWITCH statement was correct, the only thing that I can think of is that the SWITCH statement was being overloaded.
Has anyone come across anything similar? Or have I cocked up somewhere? I really hate having to use nested IIF statements for this

[DimPrawn]

I found this on Google:

For Case, depending on what you need to do, you can either use Choose, IIf,
or Switch.


Choose:
Choose(index, choice-1[, choice-2, ... [, choice-n]])


The Choose function syntax has these parts:


Part Description
index Required. Numeric expression or field that results
in a value between 1 and the number of available choices.
choice Required. Variant expression containing one of the possible
choices.


Choose returns a value from the list of choices based on the value of index.
If index is 1, Choose returns the first choice in the list; if index is 2,
it returns the second choice, and so on.


Example:
=Choose([ShipperID], "Speedy", "United", "Federal")


You can use the Choose function to create a calculated control whose value
is determined by the value of a field in a table in your database. For
example, suppose you have a Shippers table that contains a field called
ShipperID. You could create a calculated control on a form to display a text
name for the shipper based on the value of the ShipperID field.


IIf:
IIf(expr, truepart, falsepart)


The IIf function syntax has these named arguments:


Part Description
expr Required. Expression you want to evaluate.
truepart Required. Value or expression returned if expr is True.
falsepart Required. Value or expression returned if expr is False.


Example:


= IIf([OrderAmount] > 1000, "Large", "Small")


Returns the word "Large" if the amount is greater than 1000; otherwise, it
returns the word "Small".


Switch:
Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])


The Switch function syntax has these parts:


Part Description
expr Required. Variant expression you want to evaluate.
value Required. Value or expression to be returned if the
corresponding expression is True.


Example:


= Switch([ShipCity] = "Madrid", "Spanish", _
[ShipCity] = "Berlin", "German", _
[ShipCity] = "Torino", "Italian", _
[ShipCountry] = "France", "French", _
True, "English")


If the city is Madrid, the Switch function returns "Spanish"; if it is
Berlin, it returns "German"; and so on. If the city is not one of those
listed, but the country is France, it returns "French". If the city in
question is not in the list, the Switch function returns "English".


HTH