Switch Function

[SHAWTY721]

I have a list of 11 dept numbers that I need to be changed to the correct number. I tried using the Switch Function to do this, but after I create the Function it is telling me that "This expression you entered contains invalid synatx. You may have entered an operand without an operator."

Here is the switch function I am using.
Switch([AnalysisDept]=45H2,4502,[AnalysisDept]=79H1,7922,[AnalysisDept]=79H2,7982,[AnalysisDept]=79H3,7983,[AnalysisDept]=79H5,7999,[AnalysisDept]=79V3,7963,[AnalysisDept]=79V4,7964[AnalysisDept]=8500,7900,[AnalysisDept]=8501,7901,[AnalysisDept]=8505,7905,[AnalysisDept]=8511,7911).

When I just use 8500, 8501, 8505, and 8511 the result is #Error.

I am trying to figure out what I am doing wrong or it I should be using a different approach to get the result that I need.

Thanks!

 

[lameid]

I personally have never used the switch function. For what you are doing I would create a table that has the original department and the new department. Then I would outer join to this table and use the new department field as appropriate.

For what it is worth, after looking at the help topic it looks like you are using the switch function properly.

 

[MajP]

Like lameid said once you build your table of old ID and new ID then you can use an update query. If this is a primary key ensure that you have cascade updates set to true. The switch function has some possibly undesirable features:

Switch returns a Null value if:
None of the expressions is True.
The first True expression has a corresponding value that is Null.
Switch evaluates all of the expressions, even though it returns only one of them. For this reason, you should watch for undesirable side effects. For example, if the evaluation of any expression results in a division by zero error, an error occurs.

 

[lameid]

Here is the switch function I am using.
Switch([AnalysisDept]=45H2,4502,[AnalysisDept]=79H1,7922,[AnalysisDept]=79H2,7982,[AnalysisDept]=79H3,7983,[AnalysisDept]=79H5,7999,[AnalysisDept]=79V3,7963,[AnalysisDept]=79V4,7964[AnalysisDept]=8500,7900,[AnalysisDept]=8501,7901,[AnalysisDept]=8505,7905,[AnalysisDept]=8511,7911).

Caffeine finally started to kick in ...

It looks like your field [AnalysisDept] must be text and you do not have double quotes around your string values

i.e. [AnalysisDept]="45H2"
instead of [AnalysisDept]=45H2

 

[ZmrAbdulla]

Setting a table is a good approach so that you can change values any time.

Also you can have a Function to show a replaced values like below.

Public Function CDC(ByVal DeptCode As String) As String
    Select Case DeptCode
    Case Is = "45H2"
        CDC = "4502"
    Case Is = "79H1"
        CDC = "7982"
        '.........
        '.........
    End Select
End Function

Then you can use it from a query.

NewCode: CDC([DeptCode])

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[Stoneuk]

I think it is the fact that you have missed a comma in the line

[AnalysisDept]=79V4,7964[AnalysisDept]=

There should be another comma after 7964

[AnalysisDept]=79V4,7964,[AnalysisDept]=

The fact that your code works up until this points proves this.

 

[lameid]

Good eyes Stoneuk, that is a glaring syntax error.

I think it is fair to say once you get this many data elements you are better off using either the table I recommended or the Select Case statment ZmrAbdulla recommended just for the sake of being able to find syntax errors easier. Any time I can write something that can be modifed through data (table) versus code I almost always take the data approach. It makes things a lot easier to maintain. Using the table example, someone that does not write code could add updates to the Department list. This is usually a desirable thing but you may not want to make it that easy so you can ensure integrity.