Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ActiveX data Objects, Decimal Type and Oracle (97 SP2)

    I have written some code to connect to Oracle using ADO 2.6.

    Consider: varTemp = rs.Fields(1).value
    If Oracle datatype is Date, Number or Varchar2, the following snipit works fine.

    cells(1,1).value = varTemp.

    However if Oracle datatype is Number(n) (i.e. Number(5)) the snippit cells(1,1).value = varTemp produces a 1004 runtime error.

    Further inspection using Typename(varTemp) shows that the VBA datatype is Decimal. Therefore I tried: Cells(1,1).value = Cdec(SomeNumber) and sure enough a 1004 runtime error occurs.

    I fixed the code with If typename(varTemp) = "Decimal" then varTemp = cDbl(varTemp).

    Can anyone enlighten me on the Decimal data type. Excel obviously does not handle it. Excel 97 also does not play well with others. I will try this on Excel 2000 later.

    Thanks

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: ActiveX data Objects, Decimal Type and Oracle (97 SP2)

    I know nothing about Oracle, so this may be a useless suggestion. You already know that Decimal is a form of Variant Dimension. What if you changed varTemp to be Dim'ed as String instead of Variant and let Excel figure out the data type when

    Cells(1,1).Value = strTemp

    is run?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveX data Objects, Decimal Type and Oracle (97 SP2)

    Decimal number had no decimal part. In Oracle Number(5) limits the size to 99999. Number (5,2) limits to 99999.99. In this case, the Oracle data type is Number(5).

    As a side issue. Did you used to be on Compuserve or Dephi? I seem to recognize the handle from somewhere.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveX data Objects, Decimal Type and Oracle (97 SP2)

    Reading the Oracle value into a string works well also, as does testing the variant data type. So now I can get it to work, but I do not understand it. The decimal data type is addressed in Excel's VBA help, but as is often the case, the information given is virtually useless.

    I was attempting to write a class which I could resuse, but these undocumented suprises may surface again with a new and differnt dataset. The reason I need a class is to overcome the shortfalls of MS Query.

    Thanks for the help.

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: ActiveX data Objects, Decimal Type and Oracle (97 SP2)

    <P ID="edit" class=small>Edited by WebGenii on 01-Apr-02 20:53.</P> <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Cecil

    By any chance does this decimal number have .848?

    There is an MSKB article ]Q161234 that decribes something weird. Check it out and let us know.

    Also check the following:
    Q78113 Excel: Floating-Point Arithmetic May Give Inaccurate Results


    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •