Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    7

    Unanswered: Formatting numbers in a Union query

    I have created a union query and would like to format the numbers so that all get two decimals. I changed the format in the underlying queries, but still get too many decimals in the union query.

    I guess I have to write som SQL-code, but don't know how.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums.

    What is the Union Query that you have right now? Without knowing more about the query I can only point you in the direction of the Format function.
    Me.Geek = True

  3. #3
    Join Date
    May 2009
    Posts
    7
    The union query looks like this:

    Select VendID, Voucher, InvoiceNumber, FunctionalAmount
    From qry1
    UNION ALL select VendID, Voucher, InvoiceNumber, FunctionalAmount
    From qry2
    UNION ALL select VendID, Voucher, InvoiceNumber FunctionalAmount
    From qry3;

    I have used the format function for the underlying querys, but don't know if it is possible in a union query.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You are on the right track -- using the Format function will work in a union.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2009
    Posts
    7
    I have tried to use the format function in the Union query, but couldn't find out how.

    Could anybody help?

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    Off the top of my head (untested), I think anywhere you see the field you want to format, let's call it fldNumber, you replace it with Format(fldNumber, "0.00"). Give it a shot, you may need to tweak it as needed.
    Me.Geek = True

  7. #7
    Join Date
    May 2009
    Posts
    7
    Thanks! I got the two decimals by using this code:

    format([FunctionalAmount], "0.00")

    However, the values were converted to text when I ran the query, so I changed the code:

    val(format([FunctionalAmount], "0.00"))

    Then I got numeric values again, but there were no decimals. I also tried the round function, but there were still no decimals:

    val(round(format([FunctionalAmount], "0.00"),2))

    Any idea how to proceed?

Posting Permissions

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