Table defined number or date converts to text when read in VBA

[VicM]

Folk,

I have a table of data which changes monthly and is used to update the main table on a monthly basis. The table containing the data to be updated has various columns defined as Number, Text or Date.

During the update process in VBA, when number or date data is read into a VBA variable defined as Variant it's converted to a text enclosed in quotes.

Is this a quirk of using a Variant? I use that type because of the type variation in the import data. I was unaware that Variant type converts everything to text. I suppose I assumed (bad) that Variant would keep the type of what is read into it.

Thanks,

Vic

 

[MajP]

Without seeing the code, I am surprised that would happen. I think I would do something like

public sub YourCode(varImport as variant)
  if not isNull(varImport)
    if isDate(varImport) then
      varImport = cdate(varImport)
      'do some code here for dates
    elseIf isNumeric(varImport then
      if int(varImport) = varImport then
        clng(varImport)
        'do some code integer values 
      else
       cdbl(varImport) 
        'do some code here numeric non integer
       end if
     else
      'do some code here for text
    end if
  end if

 

[VicM]

MajP

I too was surprised. I create an exception report when import data changes existing data. I couldn't understand why when my code tested the import against what was in the main table, knowing they were both the same, it sent the info to the exception report. For instance it would say: Status changed from Active to Active.

Knowing which fields are numeric and which are dates, I've included If traps on those fields and change them accordingly with CInt or CDate.

Thanks,

Vic

 

[Andrzejek]

You need to be careful with [tt]IsNumeric[/tt] function.
It does not check if all 'characters' are digits.

Consider this code:

Dim str As String
str = "123[red]e[/red]45"

If IsNumeric(str) Then
    Debug.Print str
End If

str = "123[red]d[/red]45"
If IsNumeric(str) Then
    Debug.Print str
End If

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Scientific notation.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[VicM]

Andy,

I'm not using the IsNumeric function.

In my code I know which columns are supposed to be numbers and dates. When I encounter one of those columns I use either CInt() or CDate().

Vic

 

[hambakahle]

The type conversion functions (cdbl, cint, etc.) with unknown input have hazards similar to IsNumeric() plus potential overflow problems.

Dim s As String
s = "1239d45"
Dim dbl As Double
dbl = CDbl(s)
MsgBox CStr(dbl)

displays 1.239E+48

"123d945" generates an overflow on casting to double

What about testing with VarType(var)?

 

[VicM]

hambakahle

Thanks for your suggestion.

But two columns of the numbers in my input barely approach 30; and the other is just now approaching 10,000 after decades of number assignment. And there won't be any letters in any of them. So far my changes work well. And probably will do so long before any of the companies go out of business! LOL

Vic