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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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