Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2005
    Posts
    196

    Unanswered: Formatpercent error

    I'm building a query and formatting two of the fields as percent. When I add them togather, it generates an error. It adds up fine when I don't format percent one of the fields. How can I foramt both fields as percent and addd them together succesfully?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    when you calculated/foramtted your fields as percentages Access converrtted the field types to string / text, and you cannot add to string text/fields together. so you need to
    either
    coerece those values back to numeric
    OR
    re do the calcualtion
    OR
    ..better yet.. leave the formatting to the presentation layer (the form the report or whatever) and leave the calculation in the query. There are times when you may want to have formatting in the query / data manipulation layer, this isnt one of them. For example if you need decimal precision (say you are caluculating numeric values which are currency (eg when calculating sales tax or pricing products which are quoted as x.xxxxxxxxp per item)

    if you sales tax is 17.5% then 10 individual items at 1.34p is 1.57 (15.70p), 10 items are 15.75p. it may not be a lot but those rounding calcualtion discrepancies can be a beggar to track, and can make a big difference at the end of the year. After all why give the satet more in tax than it actually deserves
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2005
    Posts
    196
    Many thanks for your help. The reason i was asking the question was because I'm outputting straight to Excel. You say formatting is best done at the presentation layer - I presume this is possible with Excel?
    Next question on this topic - when I'm outputting the query to Excel, some of the numeric fields are going into Excel as text fields, and giving thast annoying error in Excel when adding the whole column together - is there any way of making the query output to Excel where all fields that are converted to numeric, go into Excel as numeric?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if its coming from Access / JET then the data should be the native data type for the column you specified, if you start applying any formatting as part of the query then Im pretty certain the datatype from format is string/text.

    thats just one of the many reasons why you are better off doing the formatting in the presentation layer. For example if you format a column to say a currency then trying to sort on that column will give silly results (as with virtually any numeric column....)

    the best thing in my view is to do the calculation in Access / JET and set the formatting in Excel. Excel knows the columns are numeric and will handle them correctly. Failing that you could send the raw data to the Excel sheet and do the calculations in Excel
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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