Results 1 to 6 of 6
  1. #1
    majoh60 is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2013
    Posts
    10

    Not Able to Total Field as Numbers

    I am very new to Access. I am designing a database to help with government report that we must complete quarterly. I made a query and then designed a report based on that query. When we report to the government, they want the amounts for each transaction reported like this
    (50,000.00 must be reported as 50). I have attached a screenshot.

    In the query field I used this format FormatAmt: Format([Amt],"0,") however when I try to total this field on the report, the only options I have are to "count records" or "count values". What I need is for that column to total as numbers because there is an additional reporting requirement when a certain threshhold is reached. Sorry if this sounds stupid but I am realyy a nebie and this is my first stab at this.
    Attached Files Attached Files

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    The Format function returns a Text value, not numerical. Get rid of it. If you want to change how the numbers look on the report, do it in the Text Box that returns the value in the Report itself. That way, you leave the numbers in numerical format and can still perform mathematical computations on them.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    are you rounding or just reporting the thousands column?

    If you're rounding you just have to divide the number by 1000 and round to 0 decimal places.
    [YourValue]/1000 in a query or report

    i.e. 50,501 would become 51
    if you want to report the same number as 50.50 just change the rounding to 2 places.

    if you want to round any portion short of 51,000 as 50 you'd do something like

    int([YourValue]/1000)

  4. #4
    majoh60 is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2013
    Posts
    10

    Update a calculated field in a query

    I am very new to Access 2007 and I posted a message yesterday to get help creating a calculated field that would take a number such as 1500 and round it to 2. I got great responses and was able to do it. My problem now is with one amount. This information is for a government report so the data has to be very accurate. All of the numbers round correctly except 500.00. 500.00 needs to round up to 1 but the querry formula rounds it down to 0. This is the only number that is behaving this way which is strnge in itself. Is there a way to update the calculated field so that $500.00 will automaticaaly round to 1 instead of 0? I hope this makes sense. I am attaching a screen print of my query results for review.
    Attached Thumbnails Attached Thumbnails TranCodeQuery_Page_1.jpg  

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632

    Round vs Format

    Note that I merged your two threads as this is really a continuation of same topic.


    This is because Round() in Access follows an even/odd rule and apparently 0 gets the even treatment. Example:

    Round(4.4,0) = 4
    Round(4.5,0) = 4
    Round(4.6,0) = 5
    Round(3.5,0) = 4
    Round(0.5,0) = 0
    Round(2.50085,0) = 3
    Round(2.5) = 2

    However, Format() does not follow even/odd rule. Example:

    Format(4.4,"0") = 4
    Format(4.5,"0") = 5
    Format(4.6,"0") = 5
    Format(3.5,"0") = 4
    Format(0.5,"0") = 1

    Convert the string that results with Format back to a number with Val()

    Val(Format(4.5,"0"))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    majoh60 is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2013
    Posts
    10
    Thank you, Thank you, Thank you. It worked like a charm.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to total a column of numbers in a subform?
    By snowboarder234 in forum Forms
    Replies: 3
    Last Post: 04-24-2012, 09:39 AM
  2. Need to autocalculate the total field
    By pratim09 in forum Forms
    Replies: 5
    Last Post: 08-09-2011, 12:17 PM
  3. Grand Total Field ???
    By damo1995 in forum Forms
    Replies: 6
    Last Post: 02-10-2011, 02:43 PM
  4. Total field with access
    By rev_ollie in forum Queries
    Replies: 0
    Last Post: 04-27-2010, 08:54 AM
  5. Creating field based an another field's total
    By yashysmashy in forum Queries
    Replies: 0
    Last Post: 09-02-2009, 03:48 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums