Two Functions You Don’t Use Enough

Wayne Wallace shows you how to use two functions (Eval and Format) to do things that you might not have thought of.

One of the great things about working in the Access environment is the number of functions that are built into VBA. The only problem is that there’s no central repository for VBA functions. It would be convenient if there was someplace where you could just type a period and get a dropdown, IntelliSense list of VBA functions. Until that function comes along, here are two functions that you may not have heard of or, if you have heard of them, two functions doing things that you might not have thought of.

Eval

This is an amazing function and one that’s only available in an application development package like Access. The Eval function (short for Evaluation) allows you to execute a code statement. All you have to do is create a string that holds the line of code that you want to execute and pass it to the Eval function. This code, for instance, puts the number 4 into the variable ing:

Dim strTwo As String

Dim strStatement As String

Dim ing As Integer

strTwo = "2"

strStatement = strTwo & " + " & strTwo

ing = Eval(strStatement)

For a function like Eval to work its magic, your application execution environment must have access to the language’s interpreter. Visual Basic 6 programmers can’t have an equivalent to the Eval function because VB6 applications are distributed without the Visual Basic compiler. But, because Access is distributed with the VBA interpreter, we get to use the Eval function. The downside of having access to this feature is that distributing the VBA interpreter with Access applications makes the Access distribution package quite large.

But the key issue is what you can do with the Eval function. The Eval function lets you deliver applications that can create themselves dynamically at runtime. You can build your application code at runtime, pass it to the Eval statement, and have your dynamically generated line of code processed.

For instance, imagine that your application accepts a wide variety of input from your users and has to call special processing depending on the kind of data being processed. You could write a massive Select Case statement that checks the type of data being input and calls the right function:

Select Case (Me.txtProcessingType)

   Case "Update"

      Call UpdateRoutine

   Case "Delete"

       Call DeleteRoutine

   Case "Add"

       Call "AddRoutine"

End Select

A simpler solution is to use the Eval function. Since the subroutines that are being called have names that that are based on the value in txtProcessingType, you can just do this:

Eval Me.txtProcessingType & "Routine"

You can even pass parameters to a subroutine that you call with the Eval function. This code passes the value of the strName parameter to a routine called ProcessName:

Eval "ProcessName '" & strName & "'"

If the Eval function is passed the name of some other function (including one of your own), the Eval function will call the other function and return the result. This code sample passes the numbers 2 and 3 to a function called AddNumbers and puts the result in a variable called intResult:

intResult = Eval("AddNumbers(2, 3)")

How might you use this? One way that I can think of is to create a table of function names with the key in the table tied to some processing code. Your program could then retrieve the function associated with the processing code by reading the function from the table. Here’s that code:

Dim rst As New ADODB.Recordset

rst.Open "Select FunctionName " & _

         " From FunctionTable " & _

         " Where ProcessingCode = '" & _

         Me.txtProcessingType & "';", _

         CurrentProject.Connection

Eval rst("FunctionName")

This code would let you add new functions to your application (or change the function to be used for some processing type) just by adding a new processing type/function name combination to the table. There’s another benefit: You would validate processing types by checking to see if the processing code is in the FunctionTable.

Format

If you build SQL statements in your code, then you know that your most common problem is getting the format of your SQL statement correct. For instance, can you spot what’s wrong in the following example?

Dim strSQL As String

Dim strCustId As String

strCustId = "A4293"

strSQL = "Select * From Customers" & _

         "Where CustId = '" & _

         strCustId & ";"

There are at least two problems with this code:

  • There’s no space after Customers, so the Where is going to be pushed right up against the table name, giving this: …From CustomersWhere CustomerId…
  • There’s no closing single quote after the customer identifier, so the Where clause is going to look like this: …Where CustId = ‘A4293;

If you’re like me, you’ve probably made both of these mistakes and a whole lot more besides. The problem is that you have to imagine what your SQL statement will look like after your test value has been concatenated into it. The Format function can simplify the creation of SQL statements.

The Format function is intended to be used to help you format dates, numbers, and strings. However, you can also use it to insert strings into another string. The Format function accepts two parameters in this scenario: The first parameter is your test value, and the second parameter is your SQL statement with a marker as to where the test value is to go. The Format function will insert your test value into your SQL statement. This lets you type in your SQL statement as a single string with no concatenation required.

There are three tricks to use here:

  • Use a series of ampersands as the marker in the SQL statement where you want your test value to go. There must be at least as many ampersands in the series as there are characters in the longest test value.
  • Put an ampersand at the front of your SQL statement.
  • Put a blank at the front of your test result.

So going back to my previous example, I can use the Format command to create my SQL statement. I pass my test value (strCustId) as the first parameter to the Format function with a blank added to the front. I pass my SQL statement as the second parameter (ideally with my entire SQL statement on one line, but I’ve had to break it up here to fit in a print column). In the SQL statement, I add an ampersand to the front and insert ampersands into the place where I want to put my test value. All I need to do is catch the result of the Format function to have my SQL statement:

strSQL = Format( _

  " " & strCustId, _

  "&Select * From Customer " &

  "Where CustId = '&&&&&&&&';")

The value that ends up in strSQL will look like this:

Select * From Customer Where CustId = 'A4293';

I can also use the Format function to insert parameters into an Eval string. This example retrieves the name of the function from a table and then inserts the value in strParameter to act as a parameter to the function:

Eval Format(" " & strParameter, _

    rst("FunctionName") & " '&&&&&&&&&' ")

Just make sure to leave any formatting characters out of your command!

Your download file is called Wallace_Functions.accdb
This entry was posted in VBA. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.