CASE Statement in SELECT

[smclellan]

Does anyone know if its possible to perform a CASE Statement in a SELECT statement in Access and if so what the syntax is? Thanks in advance.

 

[boblarson]

No, sorry you can't use a Case Statement in a Select statement. But, you can use the Switch function to simplify an IIF.

 

[allan57]

Select Case statements can be nested. Each nested Select Case statement must have a matching End Select statement.

 

[davea300]

Heres a Select statement that works on age bands, maybe you are after something like this?

If (Me.GenderAdult1 = 1) Then
Select Case DateDiff("d", Me.txtDOB, Now()) / 365.25

Case 16 To 17.99
Me.mainadult1male1617 = 1
Case 18 To 24.99
Me.mainadult1male1824 = 1
Case 25 To 64.99
Me.mainadult1male2564 = 1
Case Is >= 65
Me.mainadult1male65 = 1
End Select
End If

 

[boblarson]

davea300 - Actually you made me think of something else they can do. You can refer to a function within a SQL statement, so you could create a function that does what the case statement does and use that function within your SQL statement.

 

[smclellan]

Thanks for the help guys but I think I've found a solution by using the switch function:

SELECT Switch([activeEmployees].[Phone]="",(SELECT active.[Phone] FROM activeEmployees AS active WHERE active.[Employee ID] = activeEmployees.[Reports To ID (Contractor)]),[activeEmployees].[Phone]<>"",[activeEmployees].[Phone]) AS Phone
FROM activeEmployees

But I am interested in the possibility of referencing an external function from within my SELECT statement, how would you do that? Thanks again

 

[boblarson]

Actually, I'm not totally sure as I don't use them, but I think there may be some posts you can find here on the forum.

 

[smclellan]

Never mind Bob figured it out, I referenced a function I had programmed earlier in a module called GetPhone() the exact same way I would in VBA code:

SELECT Switch([activeEmployees].[Phone], GetPhone(activeEmployees.[Reports To ID (Contractors)]),[activeEmployees].[Phone]<>"",[activeEmployees].[Phone]) AS Phone
FROM activeEmployees

Thanks for your help guys... your really quick with the responses.

 

[Rich]

What's the point of the Switch function in the above statement?

 

[smclellan]

Well actually now that I can reference the GetPhone() function its not really necessary. Before it was used to determine if an employee had an extension and if not grab their "Contract" managers extension.