505.369.1650 [email protected]


In Microsoft Access, there is a Switch function that can be used in SQL statements to evaluate a list of expressions and return a value.  For example:

Switch([BrakePad] = "1","OK",[BrakePad]="2","Needs Adj.",[BrakePad]="3","Bad") As Brake

The expressions come in pairs.  The first expression that evaluates as True will pass back it’s associated value.  In this case, if [BrakePad]=”2″ evaluates as True, then the field Brake will have a value of “Needs Adj.”.


When converting from Microsoft Access SQL to SQL Server’s TRANSACT-SQL, sometimes the two languages have an identical function.  In this case, they do not.  TRANSACT-SQL has a CASE expression that can be used instead.  The equivalent TRANSACT-SQL statement for the example above would be:

Brake = CASE WHEN [BrakePad]='1' THEN 'OK' WHEN [BrakePad]='2' THEN 'Needs Adj.' _
WHEN [BrakePad]='3' THEN 'Bad' END

The Conversion

The VBA function below will automatically convert a Switch function to a CASE expression.  You may call the function from the Immediate window, but you will need to replace double quotes with two double quotes, or use single quotes around the arguments (remove the underscore).

?BuildCASEFromSwitch("Switch([BrakePad] = ""1"",""OK"",[BrakePad]=""2"",""Needs Adj."", _
[BrakePad]=""3"",""Bad"") As Brake")

I needed to do more than one conversion, so I created an Access form with a text box to enter the Switch statement, a second text box to receive the CASE statement, and a button that calls the function.

NOTE: In SQL view of a query, the field name will be at the end of the expression for the field (like “As Brake” in the example above).  That is also where the field name must be in SwitchExpression when you run the function.

Public Function BuildCASEFromSwitch(SwitchExpression As String) As String
' This procedure converts an Access SQL Switch() statement to a
' Transact-SQL CASE statement.
' Example:
' Switch([BrakePad] = "1","OK",[BrakePad]="2","Needs Adj.",[BrakePad]="3","Bad") As Brake
' Passes back:
' Brake = CASE WHEN [BrakePad]='1' THEN 'OK' WHEN [BrakePad]='2' THEN 'Needs Adj.'
'   WHEN [BrakePad]='3' THEN 'Bad' END
' BuildCASEFromSwitch() Version 1.0.0
' Copyright © 2023 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected].
' Use (at your own risk) and modify freely as long as proper credit is given.
    On Error GoTo Err_Handler

    Dim strExpression As String
    Dim strCASE As String
    Dim avarSwitch
    Dim strSwitch As String
    Dim strField As String
    Dim intPos As Integer
    Dim intElem As Integer
    Dim blnWhen As Boolean
    ' Replace " with '.
    strExpression = Replace(SwitchExpression, Chr(34), "'")

    ' Get the field name.
    intPos = InStrRev(strExpression, " AS ")
    strField = Trim(Right(strExpression, Len(strExpression) - (intPos + 3)))
    ' Break out the guts of Switch.
    intPos = InStr(1, strExpression, "(")
    strSwitch = Right(strExpression, Len(strExpression) - intPos)
    intPos = InStr(1, strSwitch, ")")
    strSwitch = Left(strSwitch, intPos - 1)
    ' Turn it into an array.
    avarSwitch = Split(strSwitch, ",")
    ' Loop through the array and build the guts of the CASE statement.
    blnWhen = True
    strCASE = ""
    For intElem = 0 To UBound(avarSwitch)
        If blnWhen Then
            strCASE = strCASE & " WHEN "
            strCASE = strCASE & " THEN "
        End If
        strCASE = strCASE & avarSwitch(intElem)
        blnWhen = Not blnWhen
    Next intElem

    ' Add the field name, CASE, and END.
    strCASE = strField & " = CASE" & strCASE & " END"

    BuildCASEFromSwitch = strCASE

    On Error Resume Next
    Exit Function

    MsgBox Err.Number & " " & Err.Description, vbCritical, "BuildCASEFromSwitch()"
    BuildCASEFromSwitch = ""
    Resume Exit_Proc
End Function