Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: Custom format of a Report's Cell

    Hi All,

    I have another question (I assure you that I have done some search and research before asking in this forum :-) ) that seems simple but can't find help for.
    I have a report with some cells and in each of these cells the data is of the form:
    Code:
    fraction & "(" & integer & ")"
    such that I want it to look like for example: 75%(3)
    Currently it would just display 0.75(3). How do I set it to display the format I want? Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    something like

    Code:
    =ROUND(fraction * 100,0) & "%(" & integer & ")"
    ????
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2006
    Posts
    111
    Hi, I have a report, the source of which is coming from a query to a sql database via stored procedure. One row of this report have cells whose source is of the following form:

    =Max(IIf([order]=2,[referral]/[consec_pay],0)) & "(" & Max(IIf([order]=2, consec_pay, 0)) & ")"

    So, if there is a row in the sql query (from the stored procedure) result which has
    [order] [referral] [consec_pay]
    2 4 5

    and this is the maximum [referral/consec_pay] value that a row with order = 2

    then the cell would display 0.8(5). However, how could I use the function Format() so that the cell would display 80%(5)?

    Also, I would like to limit the result of the percentage so that it doesn't display any decimal, for example in the case that:
    [order] [referral] [consec_pay]
    2 3 7

    the cell would only display 43%(7).

    I've tried different combinations of the commands including:
    Format(<expr>, "0.00%") to display what I wanted but it just won't work.

    Thanks for any help on the matter...


  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can't use format for this - you have a specific format in mind and you will have to build it up yourself. Please have another look at my post. Your expression is going to look a bit more complicated since you already have some calculations going on but that is the only way uou will be able to do it.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2006
    Posts
    111
    I was initially thinking maybe:

    Code:
    =Format(Max(IIf([order]=2,[referral]/[consec_pay],0)), "0.00%") & "(" & Max(IIf([order]=2, consec_pay, 0)) & ")"
    OR

    Code:
    =Max(IIf([order]=2,Format([referral]/[consec_pay], "0.00%"),0)) & "(" & Max(IIf([order]=2, consec_pay, 0)) & ")"
    but both didn't work...

    If I use your suggestion it would look like the following...

    Code:
    =Max(IIf([order]=2,[referral]*100/[consec_pay],0)) & "%(" & Max(IIf([order]=2, consec_pay, 0)) & ")"
    but my problem is when the fraction has more than 2 decimal places, I get for example 12.5%. How could I limit the decimal places to 0 for the expression:
    Code:
    [referral]*100/[consec_pay]
    so that in my example I wuold instead get 12% or if the fraction was say 3/7 I would get 43%(7)?

    THANKS!

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by g11DB
    but my problem is when the fraction has more than 2 decimal places, I get for example 12.5%. How could I limit the decimal places to 0 !
    That's the ROUND bit
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - depends if you want to round off or down. And there is a little gotcha with ROUND too....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2006
    Posts
    111
    thanks PF...

    i did try the ROUND function but it doesn't seem to be part of the API and even the help doesn't seem to recognize this function (I am using MS Access 97). However what worked for me is CInt() which rounds of the function to the integer value.

    best!
    -g11DB

Posting Permissions

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