asked on

how to get decimal places from fields collection in dao recordset

I am creating access tables dynamically from a recordset that is opened using an oracle query. So if the user query returns 3 fields, I create an access table with 3 columns using names from the field collection of the recordset. The problem I have is I cant see to find where the number of decimals is stored in the fields collection that is returned in the recordset. I have looked through all the properties returned for each field and dont see one. It returns a dbdecimal type, but I convert that to dbdouble and it defaults the decimal places to AUTO. I need to know the number of decimals because ultimately, this will be formatted into an excel xls (via vba) file based on the table properties. If I use AUTO for the number of decimals, when I format the vba code, I won't know how to format the cells properly. Trying to automate as much of this process as possible to eliminate user manual effort. I used access's transferspreadsheet to create the xls, but it doesn't do any cell formatting for various types. I can set the number type with number of decimals if I can determine how many decimals the field should be.FYI, I'm using the oracle odbc driver for the oracle connection.

hope that makes sense.
Thanks.

Patrick Matthews

If all you need to do is apply certain number formatting, that is easy to do...

Dim xlApp As Object, xlWb As Object, xlWs As Object

    Set xlApp = CreateObject("Excel.APplication")
    Set xlWb = xlApp.Worksbooks("path and name of the file you just exported with TransferSpreadsheet")
    Set xlWs = xlWb.Worksheets(1)

    With xlWs
        .Range("b:d").NumberFormat = "#,##0.00;(#,##0.00)" 'numeric with two decimal places, commas
        .Range("e:e").NumberFormat = "mm/dd/yyyy" 'US-style date
        .Range("f:f").NumberFormat = "$#,##0.00;($#,##0.00)" 'currency
        .Range("g:g").NumberFormat = "0.0%;(0.0%)" 'percent
    End With

    xlWb.Save
    xlWb.Close
    Set xlWs = Nothing
    Set xlWb = Nothing
    xlApp.Quit
    Set xlApp = Nothing

    MsgBox "All done", vbOKOnly, "Yeah!"

Open in new window

marfi95

ASKER

Thanks Matt.

My issue was not with the formatting part as I've done that before. The issue was really how to determine how many decimals I need so I can use the correct string. Since this is a dynamic application, lets say the oracle query submitted by the user is "select loan_amt from table". Most amount fields are defined something like 10,2 in the database, however, at time the query is run, I dont know what fields were selected. I was hoping something in the recordset field collection returned identified the number of decimal places in the returned data, so I could create my table as a double type and set the number of decimal places property (and thus excel formatting) correctly.

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marfi95

ASKER

I was afraid of that. Do you know if ADO has what I'm looking for before I make the switch from DAO to ADO ?

Patrick Matthews

Not sure if ADO is going to be any more helpful. In know that in SQL Server you could get at that info by querying some system tables; presumably Oracle has something similar.

If you do not hear from any other Experts within a few hours, I suggest you use Request Attention to ask the Mods for help, and that you specifically ask that alerts go out to Oracle experts as well.

Patrick

marfi95

ASKER

Thats true, I could do that in some cases. However, who knows what the user has requested in their query. It could be a column straight from the table, could be a calculated field with an alias, etc.. Who knows.

Thanks for your help.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.