Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    173

    Unanswered: Formatting Calculations in an ADP

    I am using a MS Access ADP connected to SQL Server Data

    In a view I have the following formula:

    dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01

    this calculates cost + markup

    I cannot get it to format in Currency

    Ex:

    Cost is $1.75
    Markup is 2.00 (2%)
    Total shows - 1.785000

    I want $1.78

    Also - I am using this formula to calculate the Quoted price for the Qty Entered

    dbo.tblQuoteItem.Qty * dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01

    Using a Qty of 2 for above, I get 3.535000

    I want $3.53


    Any help is appreciated - AB

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Set the format of your control or report field to "Currency".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by abinboston
    dbo.tblQuoteItem.Qty * dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01
    Are you sure you don't mean:

    dbo.tblQuoteItem.Qty * (dbo.tblQuoteItem.Cost + dbo.tblQuoteItem.Markup * dbo.tblQuoteItem.Cost * .01)

    ?

    Edit: Oh, and use the ROUND([yourField], 2). If you want to round down you can use the floor function, but that would require some parsing. Another option would to use str() to cast it to a string, find the position of the decimal, and drop everything more then 2 char's right of the decimal. Again, that takes some parsing, but you have options.

    If this is something you'll need to do often, you may want to consider creating a UDF to use like a function.
    Last edited by Teddy; 01-18-05 at 16:29.
    oh yeah... documentation... I have heard of that.

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

  4. #4
    Join Date
    Aug 2004
    Posts
    173

    Currency - Not Available in SQL

    Quote Originally Posted by blindman
    Set the format of your control or report field to "Currency".
    Currency is not available in a SQL/ADP Table - they use "Money" and it does not work on the calculated field....

    AB

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Duh. But this is a formatting issue, and so the formatting should be set to Currency in the Access form or report.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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