Results 1 to 4 of 4
  1. #1
    PMCOFFEY is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    CA
    Posts
    5

    Negative Value Formatting in REPORTS w/ Calculated Field


    I’m completing a report. I’ve inserted a calculated field in the report.
    I'm using Access 2010
    Here is an example:
    =([AccessTotalsOpen]-[AccessTotalsClose])/[AccessTotalsOpen]
    I get the correct answer but cannot find a way to Display/FORMAT a negative number with any of the options that I have available to me?.
    Does anyone have a method to format a calculated field in a report?.
    I tried this example but could not get it to work. =Format( FIELD ), “ 0; (0)”;
    Much appreciate any recommendations that you have.
    Kind Regards,
    Michael

  2. #2
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    try using abs([AccessTotalsOpen]-([AccessTotalsClose]/[AccessTotalsOpen])). Or int(equation). One of those should work. I'm not sure if it's a mistype but note that you are also using the AccessTotalsOpen in the first and last fields in the equation.

  3. #3
    PMCOFFEY is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    CA
    Posts
    5
    Many thanks for the reply, I tried the abs( ), it took in the field but did not produce a change in the output.
    I tried the int(). It also took in the field but did not produce a change in the data. I'm trying to get the output for negative values to appear as (####) or - ####. Neither was showing when the report was rerun. I'm going to change the boxes and rerun the report using those ideas and see if that works then. I refreshed the reports..did not work. It does not give me an error message so I'll take a few more steps and see if that works for me.
    I've searched the books and as many forum as I assess to. Nothing seems to have triggered this question to many people.
    Must be something I'm missinig on the formatting issue.
    The equation showing is for percentage change in a calculated field in the report. It provides the percentage change. The output is correct, the formatting is not.
    If I can figure out a way to show the correct formatting the reports are great. Other than that I have to upload them to a word document and THEN... do all the formatting. Lot's of extra steps I'm trying to avoid taking if I can.

  4. #4
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Have you tried changing the "Format" property of the field to Percent? If this is MSAccess 2010, I found it works well on forms. You may want to experiment with the Format and other associated properties of the text box field if the equation didn't work. I thought for sure ABS or INT would work. Otherwise I might google for "vba code convert negative to positive"

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

Similar Threads

  1. need help with formatting negative numbers
    By orientalmed in forum Access
    Replies: 3
    Last Post: 03-06-2013, 01:24 PM
  2. Replies: 2
    Last Post: 10-24-2012, 02:53 PM
  3. Conditional formatting on reports
    By JenEveAle in forum Reports
    Replies: 2
    Last Post: 12-20-2011, 10:53 PM
  4. Replies: 5
    Last Post: 12-10-2011, 11:49 AM
  5. Formatting a Calculated Field
    By e_lady333 in forum Queries
    Replies: 0
    Last Post: 03-17-2010, 06:11 PM

Tags for this Thread

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