Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Correct Number Format + Export to Excel (2003)

    Hi All.

    I've got a Table that needs the correct Custom Number format for up to 10 Billion (10,000,000,000) Plus upto 9 Decimal Places (Hopefully not Both at the same Time) , But no Decimals places to be shown if none needed (IE No white Space either). That have to be displayed with a "," (thats a comma) for each Thousand (for ease of entry Checking). Before then exporting the File (With the "OutputTo" (IE Formatted) Macro Command) to Excel and display the same format there. I've been fiddling with this (UnSuccessfully) and am Prepared to Be Humbled by your Magnificent solution (PLEASE!!!!!). Oh by the way there are never any negative numbers.

    #,##0.######### Just Dosn't do the Business. (and Leaves a Decimal Place on the Record?!!!?)

    Oh yes I need to Display a solitary 0 if the Field is Null

    Thanks In advance. I just have to get this sorted tomorrow or the girls in the office are going to be sitting around doing nothing.......

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Correct Number Format + Export to Excel (2003)

    Welcome to Woody's Lounge!

    I'm sorry to disappoint you, but such a custom number format doesn't exist. Neither Access nor Excel provides for both a flexible number of decimal places and thousands separators. Excel comes close, but it won't suppress the decimal point for whole numbers if you specify decimals.

    You could leave the Format property blank in Access, and write a macro in Excel that loops through the cells of the result of the export and sets a custom number format for each cell individually. Here is such a macro - select the cells you want to format, then run the macro:

    Sub FormatCustom()
    Dim oCell As Range
    Dim strFormat As String
    Dim i As Integer
    For Each oCell In Selection
    If oCell = 0 Or Not IsNumeric(oCell) Then
    oCell.NumberFormat = "General"
    ElseIf oCell = Int(oCell) Then
    oCell.NumberFormat = "#,##0"
    Else
    strFormat = "#,##0."
    For i = 1 To 9
    strFormat = strFormat & "0"
    If oCell * 10 ^ i = Int(oCell * 10 ^ i) Then
    Exit For
    End If
    Next i
    oCell.NumberFormat = strFormat
    End If
    Next oCell
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Correct Number Format + Export to Excel (2003)

    Ha ha ha (damn).
    I Knew it.
    Its been driving me crazy, because I thought it would be the logical primary Format (or at least it would be in the Financial Markets, where we pay Dividends that are calculated, and they insist these days that we don't drop any decimals (IE the classic rounding down scam)).

    I'll have to Compromise or reformat the excel spreadsheet in a template or just run your Macro.

    Thanks. I really appreciate it, at least now I know and won't brain myself on the desk.

Posting Permissions

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