Beyond Excel: VBA and Database Manipulation

Jan 14 2010   6:02PM GMT

Adding SQL Formulas to the Table

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

In the last post we linked the Fields Table to some code that translated the table into an SQL Select clause.  Most often, that’s enough.  But sometimes we need to manipulate the data before loading it into our result spreadsheet.  We can do that by adding SQL functions like MID( ), ROUND( ), SUM( ), or in the case below, a simple concatenate

Fields Table w/SQL Func.

Fields Table w/SQL Func.

In this example, I have added a column “SQL Func.”, hit Ctrl-Shift-N (to resize the named range “Fields” to include the new column), and entered a simple formula to combine the customer’s last name with the first name.  The result will look like “Hatmaker, Craig”.

NOTE: Microsoft’s Access and SQL Server concatenate with the “+” (plus) sign.  DB2 and Oracle use “||”.  Oracle also uses CONCAT(a, b) as does MySQL.

NOTE: Some SQL functions aggregate multiple rows of data, like MAX( ), MIN( ), COUNT( ), etc.  These SQL functions require a GROUP BY clause.  If you use one of them, you must add the proper GROUP BY clause to your SQL Select Statement or it will fail. 

Now here’s the nice part.  There’s no coding this time.  This feature/functionality (without the aggregating functions) was included in the code from the last post.

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: