Switch
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.”.
CASE
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 "
Else
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
Exit_Proc:
On Error Resume Next
Exit Function
Err_Handler:
MsgBox Err.Number & " " & Err.Description, vbCritical, "BuildCASEFromSwitch()"
BuildCASEFromSwitch = ""
Resume Exit_Proc
End Function
Making Microsoft Access Work. Call (505) 369-1650.