I have a field called GrossAmount that shows amount in two decimal format. I need to calculate a field called NetAmount where the amount needs to be rounded up. How do I do it in Access? Anyone out there to help, please?
Data type for both the fields are "Currency"
Thanks.
16 6323 ChipR 1,287
Recognized Expert Top Contributor
Since the Round() function uses round-to-even logic, I would be more likely to use: - IIf( amount = Int( amount ), amount, Int( amount ) + 1)
If the amount is an integer, return the amount, otherwise return the integer part of the amount plus 1.
FishVal 2,653
Recognized Expert Specialist
Thanks. I will try and shall get back.
ChipR 1,287
Recognized Expert Top Contributor
Thanks Fish, that's pretty interesting.
Much better.
NeoPa 32,568
Recognized Expert Moderator MVP
Certainly the best approach to the problem (that I've ever discovered).
The beauty is that the Int() function is not restricted to returning only an Integer. It's return is a Variant. It can handle even more digits than a Long. This should not be confused with the CInt() function.
Remember for currency though, that you need to multiply by 100, then divide the result, to get it to round at the penny or cent position. Be careful to ensure the calculation is not treated as a Long or Integer. If it is, the division will fail to work as expected.
ChipR 1,287
Recognized Expert Top Contributor
I just thought of
which should work since the amount should only go to 2 decimal places. Probably Int() is faster though.
NeoPa: I don't understand what you mean by multiply by 100, then divide the result, when rounding a currency or decimal to an integer.
ChipR:
Is this the correct way to write the formula in the control for NetAmount? = int([GrossAmount])
Thanks.
ChipR 1,287
Recognized Expert Top Contributor
If you want it to be rounded up, you would need to use
Since Int() rounds numbers down. When we make the number negative and round down, we get a bigger negative number, then we just negate it again and get the positive.
Like: - -Int(-[5.5]) = -Int(-5.5) = -(-6) = 6
ChipR:
It works but not the way I need it to. Perhaps I didn't explain my problem clearly. Let's say if the GrossAmount is $100.39, I need the NetAmount to show as $100. If the GrossAmount is $100.67, then the NetAmount is to read as $101.
With the formula = -Int(-[GrossAmount]), $100.39 becomes $101 as NetAmount and the GrossAmount is displayed as $100. Question: Why does the GrossAmount gets changed? It should display as what is entered, in this case it was $100.39.
Thanks.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Dalan |
last post by:
Is there any code available to address currency rounding problems in
Access 97? Apparently, selecting currency type table fields does not
resolve the problem. For instance, in my form I have a price of item
field (say $49.95), and a percentage discount field (say 10% = $5.00),
and calculated net cost field of the two. Access seemingly doesn't
understand banker's rules as the resulting total is $44.96. Could this
be a bug? Why does it...
|
by: Mike MacSween |
last post by:
This looks like a bug to me.
I have an expression on a report:
=Format(Sum((**)*(/)),"0.00")
Score is byte
PercentOfGrade is double
PropDegree is single
ModuleCats is byte
|
by: Lynn N. |
last post by:
I have a report showing Rate, Hours and Total Pay (which is
Rate*Hours) for several workers. I want to sum the Total Pay and get
a CORRECT figure. This seems like it should be such a simple task.
But there seems to be rounding issues in Access 2000 that I can't
figure out. It seems that, even though the report is displaying Total
Pay as currency rounded off to two decimal places, the report is
actually using more than two decimal places...
|
by: DaveK |
last post by:
I am upgrading an MS Access 97 application which uses the format
function to round decimal numbers to two places
e.g. format(dblValue, "standard")
Having done an initial study of this function I can see no obvious
pattern in how it does rounding, it is certainly not bankers rounding.
I need to replicate the rounding algorithm used in the new application
so that the data will be consistent, so if anyone knows the rounding
|
by: dhildebrandt |
last post by:
I have a query that uses the Round function to change decimals into
whole numbers. For 4 years straight the thing has always rounded the
numbers in exactly the same way so that whenever I update my graphs,
the percentages for the historical data are always exactly what they
were the last time I ran the query. Now, for the first time my
percentages are all just slightly off and I have not changed the query.
The only thing I can think of...
| |
by: Zorpiedoman |
last post by:
Howcome:
Dim D as decimal = .5D
msgbox d.Round(D, 0)
this returns "0"
Now when I went to school .5 rounds UP to 1 not DOWN to zero?????!!!
Documentation says this, but what the heck are they thinking??? I just don't
|
by: shaqattack1992-newsgroups |
last post by:
Hello Everyone,
I'm using the following on a form in my database:
=(-Int(-Sum((IIf(=Yes,((*)+()),
(((*)+)*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
|
by: gabo22003 |
last post by:
Hello,
I've inherited a tax report where all of the financial numbers are
currency.This is a detailed report that lists amount, tax and total for
that day ( total is listed in the database). The report gets its
information from a query. At this point all is working
Then in the report footer I am trying to total the entire amount for
that particular day. The basic formula in use is:
=Sum()
|
by: md |
last post by:
Hi
Does any body know, how to round a double value with a specific number
of digits after the decimal points?
A function like this:
RoundMyDouble (double &value, short numberOfPrecisions)
It then updates the value with numberOfPrecisions after the decimal
|
by: jacob navia |
last post by:
Hi
"How can I round a number to x decimal places" ?
This question keeps appearing. I would propose the following
solution
#include <float.h>
#include <math.h>
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |