Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    86

    Unanswered: formatt a number

    im trying to fomatt my currency. i have a value that shows 5000 it should be $50.00 heres my formula: < Look at the format piece thats were having problem
    thanks



    SELECT CFR.CFR_PATNAME, CFR.CFR_DCN, CFR.CFR_LINSEQ, Format(Val([CFR_ALLOWED])-Val([CFR_DEDUCT])-Val([CFR_OTHER])-Val([CFR_Coins])-Val([CFR_COBAMT])) AS [Form Total],(Format(CFR_PAID,".00" )) AS [CFR PAID]
    FROM CFR;

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why go through wth all the VAL(<fieldname>) rigmarole?
    How is your data stored is it numeric or string?
    if its numeric but stored as string why?, sounds like a dodgy tabel design to me.

    You don't need to perform the calculation in the query, although its often appropriate, but even if you do why place the format it in the query

    place the calculations in the query, or possibly the report, and place the formatting in the report (or table design but not the query).

    What happens if some bright spark comes along and demands report X must be sorted in ascending value order? If you have formatted in the query you are stuffed with a capital F as sorting strings will give a differenct sequence to numbers.

    Minor point please get out of the habit of having spaces in your column names. I know you can use [] to delimit columns but honestly just don't do it.

    As to why you are getting a value of 5000 in place of 50.00 I can't suggest. How is the intermediate data being stored. If the base table design was 'right' it shouldn't be a problem
    HTH

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Hi petenyce105,

    Along the lines of Healdem you are making more work of something that should be very simplistic. IF you have your field set as Currency at the TableLevel and the Decimal set at Auto, then there is no need to place it in code. Access was designed with these little thingys to make work easier without have to code every single thing. If you set your Tables up correctly there would be no need to try to do all this Formatting and stuff in a Query. Queries are mainly designed for extracting, updating and summarizing data, but wasn't meant to be a place to do all your Formatting.

    hope this gives you a clearer view as to what you need to do. Also, IF you do have a field that is set to be currency and you possibly (most cases) will need to apply some kind of math to it, then you are going to have a hiccup if you have it store as a String (Text).

    have a nice one,
    BUD

Posting Permissions

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