INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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

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

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

CODE -->

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 

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

(OP)
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

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

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

Consider this code:

CODE

Dim str As String
str = "123e45"

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

str = "123d45"
If IsNumeric(str) Then
    Debug.Print str
End If 

Have fun.

---- Andy

There is a great need for a sarcasm font.

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

Scientific notation.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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

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

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

CODE --> vba

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)?

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close