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

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » ActiveX data Objects, Decimal Type and Oracle (97 SP2)

    Author
    Topic
    #368875

    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

    Viewing 1 reply thread
    Author
    Replies
    • #579156

      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?

      • #579627

        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.

    • #579124

      Edited by WebGenii on 01-Apr-02 20:53.

      hello Cecil

      By any chance does this decimal number have .848?

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

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

      Wassim compute

      • #579626

        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.

    Viewing 1 reply thread
    Reply To: ActiveX data Objects, Decimal Type and Oracle (97 SP2)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: