Switch Statement....Who knew?

[GBalcom]

I decided to brush up on some query fundamentals through a course on Lynda.com. I didn't know that a switch statement was available right in design view. In the past I would make a custom VBA function using a select case statement. This seems a bit quicker. I wonder how the performance compares?

 

[The_Doc_Man]

The difference between an in-line statement and an external equivalent is usually the overhead of subroutine linkage calling and returning from the external functions.

You gain a SMALL amount of speed by having the function in-line. The exact amount is going to be hard to measure because it will be in units of a few microseconds.

You gain a SMALL amount of data safety for a separate function since the function could have error trapping code if that turned out to be necessary. For simple cases, it won't be, but you never know you need it - until the day you find out you DID need it.

 

[jdraw]

Good reference list of functions in Access.

 

[CJ_London]

included in JDraw's list is Choose - can be an option instead of a nested iif or switch statement

 

[Galaxiom]

Good reference list of functions in Access.

They left out the Partition() function.

https://support.office.com/en-us/article/Partition-Function-1a846a33-60c7-4371-8e77-c94278274dc5

 

[jdraw]

Agree with Galaxiom.

Here is another M$oft list
https://support.office.com/en-us/ar...category-b8b136c3-2716-4d39-94a2-658ce330ed83

It's good to have a handy reference ( or 2) with examples.

 

[Galaxiom]

The difference between an in-line statement and an external equivalent is usually the overhead of subroutine linkage calling and returning from the external functions.

You gain a SMALL amount of speed by having the function in-line. The exact amount is going to be hard to measure because it will be in units of a few microseconds.

Maybe I misunderstand your point but that is not my experience. Compare the speed of Execute using Switch() in an action query against Switch() wrapped inside a User defined function. Without actually measuring the difference, inline appears to be an order of magnitude faster. Definitely not "small".

The delay to call back to VBA is clearly huge. Inbuilt functions are compiled and supported directly by the engine so don't need to call back to VBA for interpreting.

 

[The_Doc_Man]

Depends on whether the in-line function is in a query or in VBA code.

I was comparing VBA Code with a special VBA subroutine vs. the VBA code with a Switch statement in-line.

You talked about a .Execute, which implies an SQL context. SQL is not interpretive in the same way that VBA is interpretive. Using a subroutine FROM SQL forces the Execute to "break out" of SQL context to do a VBA thing and then step back in.

Apples and oranges, I think. I fully agree with you that in SQL, there is a horrendous penalty for having to use a VBA function. I wasn't going there because I didn't read that context difference into the statement.