Introduction to Casting

Introduction

This article is about data casting. I am going to cover VBA/VB6 explicit casting.  It doesn't have anything to do with fly fishing, reel fishing, surf fishing, acting, or broken limbs.  You may have done some casting and not known it because VB does some conversions for you implicitly.  Although it is a tutorial, I'm including some tips and tricks.

Firstly, I'd like to acknowledge the genesis of this article.  The primary impetus to start writing was reading Jim Dettman's excellent article ( http:/A_12.html) on the MS-Access DLookup function.  Secondly, I had recently completed an upgrade to one of my Access applications using and enhancing the DLookup function with some data type casting.  Thirdly, from my involvement with the local Delphi user group and answering questions in the Delphi TA, I've become a fan of Delphi's QuotedStr() function.  In general, I look back on so many EE questions in various Topic Areas that involved data type casting, that I thought it worth writing an article.

Article Topics

What is Casting?

Casting causes a variable's contents or expression/literal to be treated like a different kind of data.  The data conversion takes place in a temporary memory location.  There are several ways to convert data in VB.  Since most of you should be familiar with concatenation, you should understand that concatenation is an operation between string (data type) expressions.  When you concatenate a number or date to a string, there is some implicit casting.  While there are functions that extract a part of data while changing its data type
(e.g. Format(Date(),"dddd")), this article is about a data type change of the entire data.

Common places to encounter casting

Dynamic SQL - building a SQL statement through concatenation with local variables, control values and other database data.

VBA code - converting a string to a byte array as the first part of an encryption routine.
Dim bText() As Byte
bText = StrConv(parmPlainText, vbFromUnicode)

Function and Subroutine calls - defining a parameter as a particular data type may require the calling/invoking code to cast an express to a matching data type.  Also, a routine may have to cast data passed in through a Variant parameter.

T-SQL - If you have seen many T-SQL scripts, you have probably noticed the use of the CAST() and CONVERT() functions.  

Excel - has several workbook functions to cast one type of data into another, such as DEC2HEX(), DEC2BIN(), HEX2DEC(), COMPLEX().
 

Why would this ever be a problem?

There are times when you trade convenience and flexibility for speed.  If your program reads a CSV file, you may need to read these values into local variables of String or Variant data types, then cast some of the data into specific data types for later processing.  

Problems with casting

Error 6: Overflow
Error 13: Type Mismatch
Error 49: Bad DLL calling convention
Error 1006: Unable to get the [property name] property of the [object] class
 
These statements were executed in the Immediate window.  X is a variant data type.

x=#1/2/2009#:?x,vartype(x),vbvartype.vbDate,TypeName(x),dbDate
                      1/2/2009       7             7            Date           8 
                      
                      x=1/2:?x,vartype(x),vbvartype.vbDouble,TypeName(x),dbDouble
                       0.5           5             5            Double         7 
                      
                      x=2009:?x,vartype(x),vbvartype.vbInteger,TypeName(x),dbInteger
                       2009          2             2            Integer        3 
                      
                      x=200900:?x,vartype(x),vbvartype.vbLong,TypeName(x),dbLong
                       200900        3             3            Long           4 
                      
                      x="aikimark":?x,vartype(x),vbvartype.vbString,TypeName(x),dbText
                      aikimark       8             8            String         10 
                      
                      x=true:?x,vartype(x),vbvartype.vbBoolean,TypeName(x),dbBoolean
                      True           11            11           Boolean        1 
                      

Select all Open in new window

 

date conversions
 

?cdate("1/2/2009")
                      1/2/2009 
                      ?cdate(1/2/2009)
                      12:00:22 AM 
                      

Select all Open in new window

Number concatenation -- which is why I always use the & concatenation operator
 

a=1
                      b=2
                      ?a + b
                       3 
                      ?cstr(a) + cstr(b)
                      12
                      

Select all Open in new window

Intrinsic Casting functions in VBA/VB6

 

CBool()
                      CByte()
                      CCur()
                      CDate()
                      CDbl()
                      CDec()
                      CInt()
                      CLng()
                      CSng()
                      CStr()
                      CVar()
                      

Select all Open in new window

Although not in the official list of VB conversion functions, the Val() function is quite a reliable and versatile method of casting data into a numeric value.

Note: the .Net framework is full of casting features, such as the .ToString method.  I will not cover these in this article.

Casting Tricks & Tips

Dynamic SQL
JDettman's article introduced you to delimited string variables in a DLookup() criteria parameter.  In addition, there are date variables, requiring a pound sign (#) delimiter.  Here is a general purpose function that you can use to properly delimit your variables, no matter what their data type. Sometimes we cast data by the delimiters around expressions we type into our code.  As Jim highlighted, the data type of the field needs to match the data type of the compared value/expression.  When they don't match we frequenly receive a "Data type mismatch in criteria expression" error (-2147217913), but sometimes, we just don't get the results we expected.  

 

Public Function Delimed(parmVar) As String
                        Const QuoteChar = """"
                        Const PoundChar = "#"
                        If VarType(parmVar) = vbDate Or IsDate(parmVar) Then
                          Delimed = PoundChar & parmVar & PoundChar
                        ElseIf IsNumeric(parmVar) Then
                          Delimed = CStr(parmVar)
                        ElseIf VarType(parmVar) = vbString Then
                          Delimed = QuoteChar & parmVar & QuoteChar
                        Else
                          Delimed = CStr(parmVar)
                        End If
                      End Function
                      

Select all Open in new window

Usage:
This example revisits the DLookup article's example set, comparing column values in a table.
 

Dim vCompany As Variant
                      Dim sCompany As String
                      Dim lCompany As Long
                      Dim vFrom As Variant
                      Dim vTo As Variant
                      Dim sSQL As String
                      Dim rs As Recordset
                      vCompany = 2525
                      sCompany = "2525"
                      lCompany = 2525
                      sSQL = "Select * From CompanyTable Where ID =" & Delimed(vCompany)
                      Debug.Print "vCompany=ID", sSQL
                      sSQL = "Select * From CompanyTable Where ID =" & Delimed(sCompany)
                      Debug.Print "sCompany=ID", sSQL
                      sSQL = "Select * From CompanyTable Where ID =" & Delimed(lCompany)
                      Debug.Print "lCompany=ID", sSQL
                      
                      vCompany = "EE Publishing"
                      sCompany = "EE Publishing"
                      sSQL = "Select * From CompanyTable Where CompanyName =" & Delimed(vCompany)
                      Debug.Print "vCompany=CompanyName", sSQL
                      sSQL = "Select * From CompanyTable Where CompanyName =" & Delimed(sCompany)
                      Debug.Print "sCompany=CompanyName", sSQL
                      
                      vFrom = "2001-10-1"
                      vTo = "10/11/2009"
                      sSQL = "Select * From CompanyTable Where JoinDate Between " & Delimed(vFrom) & " And " & Delimed(vTo)
                      Debug.Print "vFrom/vTo", sSQL
                      

Select all Open in new window

Result of the Delimed() function on the SQL string
 

=== Immediate Window ============
                      vCompany=ID		Select * From CompanyTable Where ID =2525
                      sCompany=ID		Select * From CompanyTable Where ID =2525
                      lCompany=ID		Select * From CompanyTable Where ID =2525
                      vCompany=CompanyName	Select * From CompanyTable Where CompanyName ="EE Publishing"
                      sCompany=CompanyName	Select * From CompanyTable Where CompanyName ="EE Publishing"
                      vFrom/vTo		Select * From CompanyTable Where JoinDate Between #2001-10-1# And #10/11/2009#
                      =================================
                      

Select all Open in new window

Warning: The Delimed() function is not perfect.  It can not overcome limitations in the runtime environment.  This is most evident with the following date example executed in the Immediate window.  If you ever created dynamic SQL with a date string that was not #-delimited, then this should help you understand what value was being passed to the database query parser.

 

=== Immediate Window ============
                      ?Delimed("12/1/2001")
                      #12/1/2001#
                      
                      ?Delimed(12/1/2001)
                      5.99700149925037E-03
                      =================================
                      

Select all Open in new window

Casting for my own convenience
In a recent application, I created a configuration settings table to help with the migration of the database from development (me), to the on-site test environment, to the production environment.  Originally, there were two text columns, containing the config-name and config-value.  I used the DLookup() function to retrieve values from the table.  I ran into some quirks of the DLookup() function that cause me to add a third text column to the table (config-datatype) and wrap the DLoopkup results in a function that cast the results into the desired data type.  This made the application code much simpler.  When the database is opened, application code can match the attached table connection string against that in the configuration settings table and reattach them, if necessary.  There are certain actions that don't work in my development environment, so I look at the DevEnvironment config-value to detect this.  When the tester gets a new copy of the application, he renames the configuration settings tables and restarts the database.  He repeats this process when moving his tested database into production.

My DLookup() problems were:


This is the function that wraps the DLookup() value from the configuration table, casting the result into the desired data type.
 

Public Function GetConfigSetting(parmConfigName As String) As Variant
                        Dim varValue As Variant
                        Dim strType As String
                        varValue = DLookup("ConfigValue", "ConfigurationSettings", "ConfigName = " & Delimed(parmConfigName))
                        If IsNull(varValue) Then
                          GetConfigSetting = vbNullString
                        Else
                          strType = DLookup("ConfigDataType", "ConfigurationSettings", "ConfigName = " & Delimed(parmConfigName))
                          Select Case strType
                            Case "Text"
                              GetConfigSetting = CStr(varValue)
                            Case "Long"
                              GetConfigSetting = CLng(varValue)
                            Case "Single"
                              GetConfigSetting = CSng(varValue)
                            Case "Double"
                              GetConfigSetting = CDbl(varValue)
                            Case "Date"
                              GetConfigSetting = CDate(varValue)
                            Case "Boolean"
                              GetConfigSetting = CBool(varValue)
                            Case Else
                              GetConfigSetting = varValue
                          End Select
                        End If
                      End Function
                      

Select all Open in new window

ConfigurationSettings table
 

ID	ConfigName		ConfigValue				ConfigDataType
                      1	ImportPath		C:\Temp\					Text
                      2	DevEnvironment		True					Boolean
                      3	BE_Connect		C:\Specimen DB 9-3\Spec Database_BE.mdb	Text
                      4	ActivityLog_Connect 	C:\Specimen DB 9-3\Activity Log.mdb		Text
                      5	Snapshot_Connect       	C:\ Specimen DB 9-3\Spec Snapshot.mdb	Text
                      6	BE_Pattern		*_BE.mdb					Text
                      7	ActivityLog_Pattern  	*Activity Log.mdb				Text
                      8	Snapshot_Pattern    	*SpecDBMS Snapshot.mdb			Text
                      
                      Fieldname		DataType 	FieldLength
                      ID			AutoNumber	4 
                      ConfigName		Text		50 
                      ConfigValue		Text		255 
                      ConfigDataType		Text		50 
                      

Select all Open in new window

ConfigDataType Lookup tab is defined as:
RowSource is a one column value list: Text;Long;Single;Double;Da te;Boolean
BoundColumn: 1
ColumnWidths: 2"

=========================================
====== BONUS  MATERIAL ==================
=========================================

Performance Tip:

About a decade ago, I gave a performance presentation to the local VB user group.  One of my tests was a Select Case statement, very similar to the one you see in the GetConfigSetting() function.  If you create a configurations settings table that is going to be executed very frequently, you would do well to change the ConfigDataType field lookup tab.  The comparison of integer values is quite a bit quicker than string value comparisons.

Also, note that the ordering of compared values will also help performance, placing the most likely encountered values nearer to the top of the list.

ConfigDataType Lookup tab is defined as:
RowSource is a two column value list: 8;Text;3;Long;4;Single;5;D ouble;7;Da te;11;Bool ean
BoundColumn: 1
ColumnWidths: 0";2"

High performance version of the GetConfigSetting() function:
 

'===================================================
                      'Performance tweaked version of the casting function
                      '===================================================
                      Public Function GetConfigSetting(parmConfigName As String) As Variant
                        Dim varValue As Variant
                        Dim lngType As Long
                        varValue = DLookup("ConfigValue", "ConfigurationSettings", "ConfigName = " & Delimed(parmConfigName))
                        If IsNull(varValue) Then
                          GetConfigSetting = vbNullString
                        Else
                          lngType = DLookup("ConfigDataType", "ConfigurationSettings", "ConfigName = " & Delimed(parmConfigName))
                          Select Case lngType
                            Case vbString
                              GetConfigSetting = CStr(varValue)
                            Case vbBoolean
                              GetConfigSetting = CBool(varValue)
                            Case vbLong
                              GetConfigSetting = CLng(varValue)
                            Case vbSingle
                              GetConfigSetting = CSng(varValue)
                            Case vbDouble
                              GetConfigSetting = CDbl(varValue)
                            Case vbDate
                              GetConfigSetting = CDate(varValue)
                            Case Else
                              GetConfigSetting = varValue
                          End Select
                        End If
                      End Function
                      

Select all Open in new window

If this article was helpful, please click the YES link below.