Rounding Dollar Amounts

Nov 9, 2006

Hello Everyone,

I'm using the following on a form in my database:

=(-Int(-Sum((IIf([LineTaxExempt]=Yes,(([Qty]*[Price])+([ShippingHandling])),
((([Qty]*[Price])+[ShippingHandling])*1.06))*100))))/100

In this case, I want to calculate a total for an order. If the
LineTaxExempt field is checked (meaning tax exempt), the total is
figured by the ([Qty] field X [Price] field)+the [ShippingHandling]
field.

If the LineTaxExempt is not checked, then the above total, including
shipping, is multiplied by 6% sales tax or 1.06. I then want this
value rounded up to 2 decimal places. This control is a running total
of the order.

This works most of the time, but I have run into a few rounding errors.

For example:

With exempt checked

1(Qty)X27.90(Price) + 6.44(ShippingHandling) gives me $34.35
instead of $34.34

With tax exempt checked (and multiple lines)

2(Qty)X141.02(Price) + 0.00(ShippingHandling)
10(Qty)X4.12(Price) + 0.00(ShippingHandling) gives me a
grand total of $323.25 instead of $323.24

It's frustrating because it seems like it works part of the time and
rounds wrong the other part of the time. By "rounds wrong", I mean I
set it wrong to round.

Is there a better formula to use in a form or query to round up to 2 decimal places other than the
=-Int(-Sum([Fieldname]) * 100) / 100 method that will give me the right results?

Any help/examples would be greatly appreciated on how to
modify this to give me the correct result each time.

Here are my field types:

QTY
-------
Field: Double
Format: Standard
Decimal Places: 2

Price
----------
Format: Currency
Decimal Places: 4 (some unit prices are 4 digits--Example $1.2525)

ShippingHandling
-------------------------------
Format: Currency
Decimal Places: 2

Thanks,

-Chad

View Replies


ADVERTISEMENT

General :: Formatting Columns To Not Round Dollar Amounts

Mar 7, 2013

I've run into a situation with our Access Database where sometimes when we import information into it from an Excel sheet the dollar amounts get rounded out. For example, the amount of $726.68 shows as that in the database but when you click on the field/cell it's in it shows as $726.6799. It doesn't do this for every field which is weird. The data from the excel sheet is not roudned out either, it shows the amount as $726.68 so it appears to be something funny going on with Access. Under the formatting area, the data type is set as currency and format is currency.

View 8 Replies View Related

Roundup Dollar Value

Apr 21, 2008

Is there a roundup function in access? I need to complete a calculation, using a rounded dollar value. If the dollar value is $15, keep it as $15...if it is anywhere from $15.01 to $15.99, I need the value to round up to $16.

View 3 Replies View Related

Format Dollar Data In Access 2000/03

Jan 3, 2007

Hi,

How do I format data in my report to display with a dollar sign?

I am using the following format $#,##0.00;($#,##0.00), but somehow I get a Format$ paramater to enter value.

Thanks

df

View 10 Replies View Related

Deleted Dollar Signs From Database - How To Put Them Back

Jul 1, 2014

I accidentally (really don't know how I did it) deleted the dollar signs from the entire database that I use daily. I did not set this database up, and don't really know how to much more than enter the data, and do basic stuff. How can I put the dollar signs back on all the forms/queries/reports that I use daily?

View 3 Replies View Related

Reports :: Currency Symbol - Print Dollar Sign In Front Of Amount

Jan 16, 2014

I have several reports where the currency was always reported in dollars so for the fields in question I had a format of $#,###.00 which printed the dollar sign in front of the amount.

As the system has now become more sophisticated I need to print out a currency symbol depending upon the client. I have a currency table that links into the client so my query 'behind' the report holds the right symbol but how do I get it ito the report. I Know I can define a second field that will hold the symbol but to me this is a clumsy way of doing it. Is there anyway I can put the symbol in the control source for the data or into the format of the field or some other way?

View 10 Replies View Related

Positive And Negative Amounts

Dec 20, 2005

I need to make a coupel of statements that display the total (sum) of negative amounts and positive amounts from a single list of results which comtains both negative and positive values. The two answers need to be displayed in the footer of a report.

View 12 Replies View Related

Adding Amounts Per Record

Jun 25, 2006

Hi to all!
I face the following problem: I cannot add amounts per line!
I have one table consisting of many different columns (fields), some of them having amounts. I want to update this table by doing the following:
let's say I have the following records:
1/6/2006 A 12
1/5/2006 B 20
15/6/2006 C 21
22/6/2006 D 17
25/6/2006 E 30.
I want to use a filter (e.g. the date being between 1/6/2006-30/6/2006), sort the records descending according to the amounts and then add in another field of the table A, B or C, according to whether the total amount including the specific record is above 70% of the total or above 90% of the total. In the specific example I want the following result:
25/6/2006 E 30 A
15/6/2006 C 21 A
22/6/2006 D 17 B
1/6/2006 A 12 C
Their total amount is 30+21+17+12=80 and the percentage of each record is: E:0.375 , C:0.2625 , D:0.2125 and A:0.15. Their adding percentage is
E:0.375 , E+C:0.6375 , E+C+D:0.85 and E+C+D+A:1.
How can I do that? I calculate the percentage of each record in a query, but how can I calculate their adding percentage, so that I can use an if-clause in an appending query?
Please help!!
Thanks in advance for your time

Panagiotis

View 4 Replies View Related

Summing By Name With Dollars Amounts

Jan 10, 2005

Heelo all:

I have a form based on a query. This form has a combobox with 4 names in it. When a person is selected form the combo box, other fields automatically populate IE: room number, address, etc. Another field is a currency box which also populates automatically.

This is what I wish to happen: When I select a person from the combo box, can it show the TOTAL amount received for THAT PERSON in a separate text box?

Any help is most welcome.

Regrds,

Dee

View 7 Replies View Related

Linking Massive Amounts Of Files

Jun 10, 2005

Hi, it's me again, the guy who works with the Amusement ride industry. I'm trying to set up a way to ogranize all our saftey bulletins that we get.

I've already looked into indexing but the major problem is that the user will need to be able to search on multiple criteria (for example the Tilt A Whirl was made by three different manufacturers). Also I can't download that nifty google tool bar because we're running win 2000 without service pac 3.

Access seemed like a great alernative, but I have about 300 or so files I need to link. Is there a faster way of linking these files than going through and doing them one by one?

I have a pre-made index the guy before me made in exel. Most all the files are in PDF format if it helps at all.
Thanks.

View 4 Replies View Related

Summing Different Multiple Amounts With Grouping

Jul 13, 2006

hey all, thanks to anyone that can help

i have 5 fields facid custid empid amount invdate.

i need to be able to get totals on the amount based on fac, cust & emp based on a date range given by user, but here's the kicker.. i need to get the current month totals, month totals - 1year (so same month but for last year)
the date range totals, last yeardate range
current ytd, last ytd

i did it using code, but the performance isn't that great. there are 2800 customers it has to cycle through and figured one of the resident experts can help with a query.
i've used dsum() but not quite getting the results i should.
i think one of the problems is the user has the option of filtering the data based on the 3 fields.

anyways if you can help i'd greatly appreciate it..

View 2 Replies View Related

Amounts In Text For Invoices....Urgent....

Feb 14, 2005

Is there any way we can convert the amount in numeric form to text format Eg: $ 1000 as Thousand Dollars.
I need this for generating invoices.

Thanks in advance for the help...

Rajesh

View 2 Replies View Related

Queries :: Only A Certain Amounts Of Row With Update Query

Feb 14, 2014

possible to have an update query to only update 20 rows with a new data in the fields? And, how do I pass the information that I want to update only data from a certain date.If I have the word Cat and want to update it to Dog, but only with the dates 12-01-2014, where do I need to put the date part (also possible to do this for dates that are greater than 12-01-2014).

View 8 Replies View Related

Queries :: Show All Amounts Past Due Date?

May 10, 2013

I have a table in this manner:

Customer | Debit Amount | Credit Amount | Date of Transaction

My goal is to have the customer's name | (Sum of Debit amount MINUS Sum of Credit Amount) and it will only show if it is past a certain due date.

View 1 Replies View Related

Tables :: Avoiding Currency Format When Storing Amounts

Feb 15, 2013

I want to store currency amounts but not use the currency format. My DB is set to use all currencies and provide in all currencies.

Do I use the SINGLE data type?

View 1 Replies View Related

General :: Adding Decimal Amounts To A Percentage Expression

Jan 4, 2013

I'm having difficulty in adding decimal amounts to a percentage expression.On a form in a text box I have a percentage expression of 73.38%. In a combo box I have a drop down menu of: 0.75, 0.5, 0.25, 0, -.25, -.5, -.75With another text box I would like to have the decimal amounts added to the percentage. For example, 73.38% + .5..Which will equal in the text box: 73.88%

View 2 Replies View Related

Queries :: Complex Query Sorting And Summing Amounts Breakdown

Jan 28, 2015

I have a shell of my database in the below link for reference. I am trying to create a query for a report that will allow me to track charges and payments per client. Each charge has a ChargeID and each payment has a PaymentID. Multiple payments can be applied to one charge so the same ChargeID can show up multiple times with a different PaymentID. Payments that are catagorized as Third Party Payments will have not only a PaymentID, but a TPPaymentID.

What I need to do in the query and final report is track the total charges (even though the ChargeID may show up multiple times, I only want the charge itself to be calculated once and the charge to only be listed once for each client) and track the total payments as well as sum the total payments for each charge for each client.

View 9 Replies View Related

General :: How To Sum Amounts In List Box Column And Show Total On Main Form

Oct 19, 2012

How can I sum the amounts in the list box column and show the total on the main form?

View 2 Replies View Related

Rounding

Jan 8, 2007

In My Table I Want To Type A Number (4.2) But I Cant Get Access Not To Round That To (4.0) Any Suggestions

View 1 Replies View Related

Rounding

Feb 12, 2008

I am trying to write a query, it needs to multiply 2 numbers from separate columns, then, I need it to Round the value UP to the nearest 0.25? Does anyone know how to write this formula in a query?

View 1 Replies View Related

Rounding

Feb 9, 2005

I am having some problems with rounding numbers. I have a DB using forms and i want the user to enter a weight and it rounds to the nearest half KG, so either ?.5 or ?.0 depending on what the number is. I know i can enter a range check in the table validation or say => 5 AND <= 10 but i am stuck on the rounding validation. I think this must be enterd into the text box of the form but as to what the syntax would be i am lost.

Thanks
Chris

View 3 Replies View Related

Rounding

Jan 8, 2007

I Want To Type 4.2 And Access Is Rounding It To 4.0...ive Tried Changing The Format To General Or Fixed But I Cant Get Access Not To Round

View 3 Replies View Related

Rounding Up

Jul 23, 2005

Have the need to Round up to the next whole. If I have 1.2, I would like it to Round to 2. Is this possible? :

View 6 Replies View Related

Rounding Up

Dec 15, 2005

I want to round up to whole number based on U. the else needs to round up to the nearest cent. i.e. 1.64 rounds up to 1.65.

The first part works but I can figure out how round up the cents in the else part.
IIf([Item type]="U",+1*Int(+1*[Gross]*1.03),([Gross]*1.03)

Any help would be greatly appreciated.

Michael

View 10 Replies View Related

Rounding Up

Oct 13, 2006

I have the following expression in a query. This gets the order due date takes away todays date then goes through a series of calculations to work out the period in terms of a number of months (Assuming they are all 4 weeks, wrong I know)

((((([Blanket_One]![Due Date]-Date())/7))*5)/20)

My problem is fairly simple I think. I need the output from this expression to always round up to the nearest whole number.

Example (1.0009 would round up to 2)

In excel I can use the function Roundup, is there an equivalent in Access?

Thanks
:confused:
Judo

View 5 Replies View Related

Rounding Up And Down

Jul 29, 2005

I have a form with a field that gives me an average of results. Below the number 10 it works fine - for example, if I have 6.3 as an average it displays 6, if it is 6.7 it displays 7.

If however the number is greater than 10 I have a problem. Should the number contain a decimal it rounds down to 10 or up to 20. ie any number below 14.9 will round down to 10 and any number above 15.1 will round up to 20. Whole numbers display fine.

Any ideas?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved