vbastringms-accesstypesvariant

Why Does VarType() Return vbString for a Variant Variable Containing a Long Integer?


What the title says.

In Access VBA, I have a function that is intended to return a criteria expression from field name and value parameters.

The idea is that for strings, the criterion will use the Like operator to permit pattern matching, e.g., Party Like '*f*' and for long integers, i.e., PK values, the criterion will use the equality operator (=), e.g., PartyID = 5822. Currently, this is conditioned on the variant subtype of the passed value parameter.

The function is below but the problem is that VarType() evaluates the Variant variable containing a long integer as being of the string subtype. I.e., VarType() returns 8 (i.e., vbString) when evaluating a Variant variable containing a long integer, whereas one would expect it to return 3 (i.e., vbLong). Tests indicate that this behavior also exists in the calling code, i.e., VarType() returns 8 (i.e., vbString) even for ctl.Value. Recall that the Value property data type of all Access controls is Variant.

The question is why this is occurring, and specifically how to get VarType to behave as documented.

Private Function CriterionCreate(ByVal strFieldName As String, varValue As Variant) As String
100     Dim strCriterion As String
    
200     Select Case VarType(varValue)
            Case vbLong     '=3.  For long integer PKs.
202             strCriterion = strFieldName & " = " & varValue
210         Case vbString   '=8.  For strings & text PKs.
211             strCriterion = strFieldName & mLikeThis(varValue)
220         Case vbNull     'Blank / empty record.
221             strCriterion = ""
229     End Select
    
300     CriterionCreate = strCriterion
End Function

I will note that the calling code iterates Form.Controls and assigns each control regardless of type to the variable ctl, which is declared As Control, not as a specific control type. The Control object does not have a Value property, although ctl.Value returns the appropriate value, even though IntelliSense won't autocomplete the property name correctly. No reason exists to think that this would affect the behavior of VarType() or the character of the data but perhaps the data somehow changes character passing via a property so specified, in which case this is an undocumented bug, besides being fundamentally inconsistent with the notion of Variant.

Edit: The calling code is:

strCriterion = CriterionCreate(strFieldName, ctl.Value)


Solution

  • Thanks to @Andre, who dropped the breadcrumb that led to a solution, which emerged after a grand tour of the ComboBox object and its properties, as follows.

    [EDIT: The following works perfectly well but it turns out the IsNumeric() function will accurately evaluate Long data returned by an unbound ComboBox that VarType() incorrectly characterizes as a String.]

    [TL;DR: An unbound ComboBox fails to return accurately typed data. So, instead reference ComboBox.Recordset to wrangle accurately typed data from an unbound ComboBox.]

    The breadcrumb is that an unbound Access TextBox returns text (i.e., a string) by default. The problem I was having was with an unbound ComboBox, but still.

    This is entirely unintuitive because the data is a Long Integer and Value properties all are Variant, which suggests that the data type would be preserved.

    Instead, it seems that ComboBox.Value evaluates ControlSource to determine its data subtype rather than the data itself. If a combo box ControlSource is a FK field in a table, that field likely is Long, so ComboBox.Value returns that type. An unbound combo box has no reference, however, so ComboBox.Value defaults to vbString, perhaps because that is the type of the ControlSource property itself.

    • Note that this issue is most likely to arise with unbound combo boxes. The Text and Value properties of an unbound TextBox will be blank. An unbound ComboBox, however, will have these properties populated by its RowSource property.

    • Thus, while ComboBox.Value appears to evaluate ControlSource to establish its subtype, it does not appear to evaluate RowSource in a similar manner.

    Nowhere, besides here, is this documented.

    So, an unbound ComboBox' Value property fails to return accurately typed data. Other data references are available, however, albeit with subtle distinctions:

    • ComboBox.Value, again, is typed vbString.
    • ComboBox.Column(0) also is typed vbString. This seems odd because ComboBox.Column presumably would reference the data directly and if so, ought to be able to correctly type it. Other behavior of the Form object suggests that objects interact internally with JET SQL dynasets, not their RecordSource, ControlSource, or Recordset objects directly. If so, then perhaps Access types all such data as vbString. Stay tuned on this topic.
    • ComboBox.Recordset.Fields(0) is typed vbLong, which is a bit of a breakthrough. Sadly, references to ComboBox.Recordset return all records. The solution, then, is to filter the recordset.

    It may seem like a bit of work but the code I came up with to wrangle accurately typed data from an unbound ComboBox object follows. Its advantage is that it does not require further inspection or any use of a type conversion function to coerce type.

    230                 If ctl.ControlType = acComboBox Then
    231                     Dim rst As DAO.Recordset
    232                     Dim rstFiltered As DAO.Recordset
    
    240                     Set rst = ctl.Recordset
    
    250                     With rst
    251                         .Filter = .Fields(0).Name & " = " & ctl.Value
    252                         Set rstFiltered = .OpenRecordset
    259                     End With
    
    260                     varValue = rstFiltered.Fields(0)
    
    267                     Set rstFiltered = Nothing
    268                     Set rst = Nothing
    269                 End If
    
    270                 strCriterion = CriterionCreate(strFieldName, varValue)