Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2004
    Posts
    5

    Unanswered: programmatically formatting a field as Percent without it becoming 'text'.

    Hi all,
    i have a query that calculates percentages. i would like to display them as %ages as follows (76.2% instead of 0.762). the FORMATPERCENT function would work, except i want the numbers to be still recognised as numbers and not text.

    if i change the field properties in the 'Query Design' view to Percent, this works. however, i am creating and running the query from VBA so i need to do this programmatically.

    Youval.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Display them in what??? Whatever you display them in is where you set a display mask ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2004
    Posts
    5
    When i wrote 'display' i didn't mean in a 'report' or anything., i just meant the resulting table you get once you've run the query.

    PS i will eventually have to figure out the best way to send the results to excel - currently i copy the resulting table and paste it to excel.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    reformat the column in excel.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Sep 2004
    Posts
    5
    is there nothing neater that can be done in vba in access??

    i've seen some code that uses the method "CreateProperty", but i don't know how to use it - is that what i have to use?

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    No, not really.

    When you copy and paste, you are effectively taking control away from access OR excel. The information you're pasting can easily be formated as any number you would like once inside excel.

    The other alternative is to take the plunge and automate it.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    instead of using formatpercent() function try to set format propery of field in your table to Percent. I don't know if it could fix your problem but worth to try.
    ghozy.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Won't matter. Once it's in the clipboard it's text.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    We're still coming back to what this poster just doesn't understand: Percentage is a freaking MASK. The number is just a number... How it works for percentage is that the number should be divided by 100 because percentage multiplies by that and then makes a mask of "#,##0.00%" ...

    I just don't see what is so hard about this ...
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    well my other workaround suggestion is, have same field without formatting in your query. and copy that field instead of formatted one.
    ghozy.

  11. #11
    Join Date
    Sep 2004
    Posts
    5
    Quote Originally Posted by M Owen
    Display them in what??? Whatever you display them in is where you set a display mask ...

    Why is it that if a field is formatted as a % using the FORMATPERCENT function, then when you copy the results of that query to excel, it copies as text, whereas if the field was formatted as a % by accessing the field properties using the 'Design View' of Access, the results of that field (when the query is run) copied to excel are recognised as numbers?

    (-This is why i was hoping to find a way to change the field Properties from VBA code.)

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It has NOTHING to do with field properties, access, excel or vba.

    When you copy ANYTHING into the clipboard with cross-platform intentions, it doesn't matter where the data came from or how it was formatted. Copy/pasting from access to excel is exactly the same as copy/pasting from a word document, webpage, misc. spreadsheet ap etc etc. There is no format or lack thereof that is going to solve your problem. You will have to reformat in excel, or have access speak directly to excel, sorry.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    as Teddy said, when you copy data to clipboard, you copy as text. it is excel's paste function interprets that text and decides if the field is number or text. you cannot fix that problem with properties of access.
    anyway I tried to make some computations with percentage fields in excel. they seem to work correct. you can multiply them with other numbers and it evaluates correct. why do you want to see percentages as decimal numbers?
    ghozy.

  14. #14
    Join Date
    Sep 2004
    Posts
    5

    OK, excel is weird! / my reason: to accumulate

    OK, excel is weird!
    my issue with these formats now boils down to this - when i highlight those values i copied from access that were a result of the FORMATPERCENT function, no sum or count or whatever is shown in the excel status bar. However, they are still manipulatable as numbers as ghozy has rightly said.
    so i guess this is now resolved.
    thank you all for your responses.

    the reason i want to sum the percentages is cause all the methods i've found for doing cumulatives in access (basically all variations on having a query within a select statement) take access way too long to do. I'm hoping there's a way in VBA to manually do the accumulation. I'll ask this in a new thread tomorrow.

Posting Permissions

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