# Thread: Formatting Calculations in an ADP

1. Registered User
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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Set the format of your control or report field to "Currency".

3. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
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.

4. Registered User
Join Date
Aug 2004
Posts
173

## Currency - Not Available in SQL

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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595