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
?cdate("1/2/2009")
1/2/2009
?cdate(1/2/2009)
12:00:22 AM
Number concatenation -- which is why I always use the & concatenation operator
a=1
b=2
?a + b
3
?cstr(a) + cstr(b)
12
CBool()
CByte()
CCur()
CDate()
CDbl()
CDec()
CInt()
CLng()
CSng()
CStr()
CVar()
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.
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
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
=== 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#
=================================
=== Immediate Window ============
?Delimed("12/1/2001")
#12/1/2001#
?Delimed(12/1/2001)
5.99700149925037E-03
=================================
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
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
'===================================================
'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
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)