Microsoft Access Tips for Serious Users

Provided by Allen Browne.

VBA Traps: Working with Variables

This article is the second in a series discussing subtle bugs that lie unrecognized in your code until particular conditions are met.

Option Explicit

If you omit Option Explicit from any module, IMHO, you deserve everything you get. A simple spelling mistake, and your code creates and initializes a new variable to a value very different from what you expect your variable to contain. Option Explicit is a great safety net for your simplest or most daring trapeze act. Omit it to save a few declarations, and expect hours of elusive debugging.

Solution:

  1. Go back and manually insert Option Explicit in the General Declarations of every existing module, included any modules associated with forms or reports.
  2. To automatically include Option Explicit in all new modules, check the "Require Variable Declaration" check box. In Access 2000 or 2002, it is under Tools | Options | Editor (from the code edit window). In Access 95 or 97, it is under Tools | Options | Module.

Assigning Null

Whenever you assign the value of a control to a variable, consider the possibility that the control may be Null. The only VBA type that can contain Null is the Variant. For all other variable types, your program will appear to work until the control contains no data. It will then generate an error.

Solutions:

Optional Arguments

Only Variants can contain the value Missing. If you assign any other type to an optional parameter it will be initialized with a value (0 for numbers, "" for strings, etc). When you test if the optional argument is Missing, Access sees it has a value. As a result, IsMissing() returns False, even if the parameter was not supplied by the user.

For example, the simple function below intends to return True if the optional argument is missing. But declaring the argument as a boolean initializes it to False. The test for IsMissing() then fails, and the function returns False as the default!

    Function TrueAsDefault(Optional bIsTrue As Boolean) As Boolean
        If IsMissing (bIsTrue) Then
            'bIsTrue is *never* Missing!!!!
            bIsTrue = True
        End If
        TrueAsDefault = bIsTrue
    End Function

Solutions:

  1. Use Variants for optional parameters, or
  2. Supply the default value in the function declaration, e.g.:
    Function TrueAsDefault(Optional bIsTrue As Boolean = True) As Boolean
        TrueAsDefault = bIsTrue
    End Function

Hint:

With Variant arguments, your procedure must test the type of the data passed in. Don't test for all the wrong types:

    If IsMissing(MyParm) Or IsNull(MyParm) Or IsError(MyParm) Or ...

Instead, test for the desired type: IsDate(), IsNumeric() etc. The example above (corrected) becomes:

    Function TrueAsDefault(Optional bvIsTrue As Variant) As Boolean
        If Not IsNumeric(bvIsTrue) Then
            bvIsTrue = True    'Default if Missing, Null, Error, invalid type.
        End If
        TrueAsDefault = bvIsTrue
    End Function

Dates in Strings

If your program may be used outside of the United States (Britain, Korea, etc.), you need to be aware that many countries use other date formats such as dd/mm/yy, yy/mm/dd. Jet SQL requires dates in American format (mm/dd/yy). Dates entered as criteria in the Query Design grid are correctly interpreted by Access.

When you build SQL strings in VBA, you must explicitly format the dates for Jet. Failure to do so means you code is broken if the user changes the Regional Settings in Control Panel. When you format the date, the Format() function replaces the slashes in the format string with the date separator defined in Control Panel. The slashes in the format string must therefore be preceeded by backslashes to indicate you want literal slash characters.

Solution:

In a general module, create a function that returns a string representing the date as #mm/dd/yyyy#.

    Function SQLDate(vDate As Variant) As String
        If IsDate(vDate) Then
            SQLDate = "#" & Format$(vDate, "mm\/dd\/yyyy") & "#"
        End If
    End Function

Always wrap dates passed to Jet in this function, e.g.:

    Dim strWhere as String
    If Not IsNull(Me.StartDate) Then
        strWhere = "[InvoiceDate] >= " & SQLDate(Me.StartDate)
    End If
    DoCmd.OpenReport "MyReport", acViewPreview, , strWhere

Automatic Typecasting

In the Immediate window, enter:

    ? 5 + 8, "5" + "8", 5 + "8"

As you would expect, the first expression (numeric addition) returns 13, and the second (string concatenation) returns 58. But what of the third expression? It does not return an error. Instead Access automatically typecasts the string 8 into a number, and proceeds with numeric addition.

Occasionally this automatic typecasting produces weird results, when Access makes a different assumption from what you intend. The problem is most prevalent with unbound text boxes and Variants. Generally the problem shows up during debugging, but it can show up after release. For example, Access may recognize a date in an unbound text box while you develop and test the application, but fail to interpret the date the same way for a user who has a different Date setting in Windows Control Panel.

Solutions:

Home Index of tips Top