Provided by Allen Browne.
This article is the second in a series discussing subtle bugs that lie unrecognized in your code until particular conditions are met.
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.
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.
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
Function TrueAsDefault(Optional bIsTrue As Boolean = True) As Boolean TrueAsDefault = bIsTrue End Function
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
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.
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
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.
|Home||Index of tips||Top|