Currency and the number of decimal places

I have an application which has neen running for a number of years, firstly 
on Access2000 then XP and now on Access 2003.

It uses many currency fields for invoicing , credit notes etc. Each field 
which is of currency type has the decimal property set to 2.
Up until recently the ATB balanced fine but lately it has been a few cents 
out. The reason is that some amounts have been put in with 3 decimal places 
and some calculations (eg GST of 10%  of  $398.68 returning to the GST field 
a value of $39.868) returning 3 decimal places.

I guess with the calculations, I can code to make sure only 2 decimal places 
are returned, but is there a way to stop the entry of 3 decimal places in a 
currency field? I would have thought that by limiting the property of the 
field to 2 decimal places then only 2 places could be entered. Wrong!

I'm looking for a quick and easy way to make sure only 2 decimal places are 
returned so that I don't have to go through all my forms and put in code for 
every currency field.
Any ideas?

John B 


0
John
5/30/2007 2:36:45 AM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
1386 Views

Similar Articles

[PageSpeed] 59

On Wed, 30 May 2007 12:36:45 +1000, "John Baker" <johnbaker@iinet.net.au>
wrote:

>I guess with the calculations, I can code to make sure only 2 decimal places 
>are returned, but is there a way to stop the entry of 3 decimal places in a 
>currency field? I would have thought that by limiting the property of the 
>field to 2 decimal places then only 2 places could be entered. Wrong!

Wrong indeed. A Currency datatype field always has four, and exactly four,
decimal places. 

You need to Round or truncate the calculations to two decimals at the time the
calculation is done.

             John W. Vinson [MVP]
0
John
5/30/2007 2:56:40 AM
Thanks John.

You have just given me the go ahead to do what I was trying to avoid doing 
but what I really need to do.

Cheers
John B.


"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:ptpp531l7ctgv8itvlav8gbqg3oi8kmn21@4ax.com...
> On Wed, 30 May 2007 12:36:45 +1000, "John Baker" <johnbaker@iinet.net.au>
> wrote:
>
>>I guess with the calculations, I can code to make sure only 2 decimal 
>>places
>>are returned, but is there a way to stop the entry of 3 decimal places in 
>>a
>>currency field? I would have thought that by limiting the property of the
>>field to 2 decimal places then only 2 places could be entered. Wrong!
>
> Wrong indeed. A Currency datatype field always has four, and exactly four,
> decimal places.
>
> You need to Round or truncate the calculations to two decimals at the time 
> the
> calculation is done.
>
>             John W. Vinson [MVP] 


0
John
5/30/2007 3:03:24 AM
On Wed, 30 May 2007 13:03:24 +1000, "John Baker" <johnbaker@iinet.net.au>
wrote:

>Thanks John.
>
>You have just given me the go ahead to do what I was trying to avoid doing 
>but what I really need to do.

Just note that the Decimal Places property of a currency (or Number, for that
matter) field only affects the appearance - what's shown, rather than what's
stored.

You can also use a Number... Decimal type and explicitly set the size of the
Decimal number to use two decimals, if you have A2003 or later.

             John W. Vinson [MVP]
0
John
5/30/2007 4:24:35 AM
Reply:

Similar Artilces:

Currency rates
Hi Everybody - I am building a smartlist with smarlist builder with our currency rates but its only shows 2 decimal places - how can I make it show 7 decimal places? In SmartList Builder, click on the field display name so it is highlighted, then click on the blue arrow next to the 'Display Name' heading. Go to the Decimal Places tab to change the number of decimal places shown. I am not sure you'll be able to see 7 though, I think 5 is the limit. If it is absolutely imperative to show all 7 decimal places, then you might need to convert this to a string (I have not trie...

Want to see as numbers not text #2
Thanks Julie I tried that but that did not help! The problem seems to be from th formula with the Vlookup function that looks into the empty cells an returns the none-number values -- chrisdtra ----------------------------------------------------------------------- chrisdtran's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1596 View this thread: http://www.excelforum.com/showthread.php?threadid=27724 Hi sorry, missed the "obvious" ... replace the "0" in your formula with simply 0 e.g. if(iserror(vlookup(C1,A1:B6,2,false),0,vlookup(C1,A1:B...

negative numbers #5
I'd like to put in some formating or a formula that if cell A1 has the word "Payout" then the numbers in cell D1 become a negative number... How can this be dones? --- Message posted from http://www.ExcelForum.com/ In D1, =IF(A1="Payout",-(Original_Formula),Original_Formula) unless of course it is possible that the original formula could have produced negative results in the first place, in which case:- =IF(A1="Payout",-ABS(Original_Formula),Original_Formula) -- Regards Ken....................... Microsoft MVP - Excel Sys ...

how to consecutive number same worksheet
For example Page 7 has multiple pages, how can I get it to read 7a, 7b, 7c and so on on the worksheet. How can one Page have several pages? Do you mean one worksheet(Sheet7) with several printed pages? Do you want a header or footer with 7a, 7b, 7c etc. on each printed page? That would require VBA Gord Dibben MS Excel MVP On Thu, 11 Feb 2010 07:56:05 -0800, Bob <Bob@discussions.microsoft.com> wrote: >For example Page 7 has multiple pages, how can I get it to read 7a, 7b, 7c >and so on on the worksheet. ...

How to set 4 decimals in the POP module
Hello: The person in charge of the purchase orders would like to have 4 decimals instead of the already configured 2, so how do i do that?? Thanks in advance Luis CAUTION...make a backup first and try this in a test company before doing it in the live company. I have done this and it can be done but there are issues. You must change the currency decimal places in the inventory items. This could affect open POs and prices (if your price list is based on a mark up from cost) You have to make this change using SQL. First change the item classes (so new items are set correctly) then...

How to Retrieve Install Serial Number
Hi All, No - I'm not after a serial number to crack VS2008. I have a legit copy already working. What I want to know is whether I can get hold of the Serial Number that I can force a user to enter during setup (Customer Information screen on standard VS installer) from within my application, so that I can perform some simple validation upon it each time my application runs. I know it won't be very secure, but it's just a mild deterrent for a low-value piece of software. I searched on the obvious keywords, and just received tons of warez sites - not surprising...

Validate the format of a number
I need to determine that an entered serial number is valid. It must check that it is 11 characters and follows the format as follows: a letter, followed by a number, followed by 2 letters, followed by 6 numbers, and ending with a letter. For example, the user enters D7PM234567B and the cell next to it would indicate 'valid' or something similar. If 87PM2345674 was entered, it was indicate 'invalid' next to it or something similar to alert the user it is not in the correct format. Thank you very much in advance. Steve This formula =AND(LEN(A1)=11,CODE(...

currency
hi, i am trying to create an if statement with different currency. for example: Col. A B C Value: EUR USD (can either be EUR, USD) (if "A2" is a Euro, then the value goes in "B2." If the Value is in US dollars, then value has to go in "C2." ) How do i write a statement to that can identify between currencies? Thank you, -- hmz nyc hmz you say:- "the value" What do you mean...

How do I set number formats that will be applied only if a number.
Hi you may post your question as body of your message :-) -- Regards Frank Kabel Frankfurt, Germany "Jim Gentile" <JimGentile@discussions.microsoft.com> schrieb im Newsbeitrag news:2678B459-1954-4258-A570-155E25F18F52@microsoft.com... > ...

Currency
I was having a problem with my printer on Wed. Tech guy looked at and fixed, but then my MS Money was now showing currency in US (I live in Canada). How do I change ALL accounts to Canadian Currency. I did the Tools, options, etc and it changed all accounts back to Canadian, except for 2 - my cash account and my Bank Chequing Account. How do I change them to Canadian Currency. THanks. Money takes its default currency from your operating system. Goto start/settings/control panel/regional settings [depending on your OS] and change whatever is there to Canadian Dollars. "Gary&...

excel 2007 chart data label
I have a chart with 3 lines with a data label. Is there any way to automatically put the Highest number to be over the line and the other line data label? -- Sue Hi, You could use another series to display data labels based on a condition. See Jon's example which does it for points, but the same principle can apply to data labels. http://peltiertech.com/Excel/Charts/PointAtPoints.html And this labels the last point but again can be adapted. http://www.andypope.info/ngs/ng17.htm Cheers Andy Sue wrote: > I have a chart with 3 lines with a data label. Is there any way to > aut...

multiple currencies #5
Hi all, I'm running Money 2005 and I want to set it up to manage my UK and my French account. I've set it up as =80 being the base currency and my French account as the main account. I've added my UK account and set it as using British pound as the currency. When I set up the UK account and set the opening balance, then returned to the account listing, it had decided the value I had entered was in =80 and so set a different value. When I then went back to set the opening balance to the equivalent value in euros and again setting the account currency as British pound, it for some r...

formating numbers #2
Hi, For some reason when I type 6.00 in my cells, excel formats my cell as 6 and removes the zeros of the fractional part. How can I overcome this problem ? Thx "Sam" <samuel.berthelot@voila.fr> wrote in message news:1112818136.718308.323460@f14g2000cwb.googlegroups.com... > Hi, > For some reason when I type 6.00 in my cells, excel formats my cell as > 6 and removes the zeros of the fractional part. How can I overcome this > problem ? > > Thx > Select the cell, column or row where you want two decimals. Select Format|Cells and click on Numbers. Make su...

Converting number to written number
Our business software doesn't allow us to easily print off a quick cheque to payees that are not first established as vendors. I am trying to set up an excel cheque template to allow me to manually print off a cheque that I can later enter into our system. I would like to enter the amount in one cell and have another cell where that amount is converted automatically to a written representation of the number. (ie: ***One thousand, three hundred and twelve and 19/100 dollars***). I suspect that I would need to create a macro that would peel off each individual digit and convert it to a ...

Is there a limit to number of successive IF-THEN statements?
I am listing a number of deliverables, and want the deadlines for those deliverables to automatically appear in the next column (I have the deadlines on a tab named "Lists"). Unless I am misunderstanding something, there is a limit of 7 successive IF-THEN statements you can use. Here is my IF-THEN formula: =IF(E4="User Guide",'Lists'!D4,IF(E4="Installation",'Lists'!D5,IF(E4="HW Upgrade",'Lists'!D3,IF(E4="Maintenance",'Lists'!D6,IF(E4="RAS Trouble",'Lists'!D3,IF(E4="Online Help"...

sum by currency
I have two column as you see below: EUR 5,000.00 USD 12,500.00 EUR 1,251.00 EUR 12,544.00 USD 5,555.00 GBP 15,455.00 How can I sum the amount Per currencies Tot GBP (function) Tot EUR (function) Tot USD (function) Hi Admin a similar question was asked earlier today - here's the answer i posted to that question couple of options - 1) use data / subtotals to automatically generate this (you can then use the outline levels to hide the details allowing you to print the summary) (ensure that you've sorted by Currency type first) 2) use a pivot tabl...

Currency not available in currency list
Hi. I need to record bank statements in Egyptian pounds, which is not a currency supported in the list of currencies you can select. How do I specify a currency and exchange rate that is not on the drop down list? Tools, Options, Currencies, Add New Currency. "Ann" <anonymous@discussions.microsoft.com> wrote in message news:227f01c3e0e1$40465380$a001280a@phx.gbl... > Hi. I need to record bank statements in Egyptian pounds, > which is not a currency supported in the list of > currencies you can select. How do I specify a currency and > exchange rate that is not ...

Different Currencies
I have Money 2004 (want to ugrade). I have recently moved to Germany and would like to start keeping my accounts in the Euro but notice that it will not allow me to. Only allows USA and Canadian currency. It seems to require me to convert to one of these currencies. I want to upgrade but I'm not wasting my money if I can't keep my accounts in Euros. Does anyone know if there is a way of changing the currency to Euro. Thanks You should be able to create new accounts denominated in Euros. I suspect you are correct that a US version will not let you default to Euros. Not being a...

Searching for multiple numbers at once...
Hello, I am new to the forum. I am wondering if it is possible to search for multiple numbers in Excel at once. Lets say I have a very large spread sheet with part numbers and I want to find 200 specific part numbers. Is there a way to search for all 200 part numbers at once, an have Excel high light them? Thanks in advance. -- josel777 ------------------------------------------------------------------------ josel777's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36989 View this thread: http://www.excelforum.com/showthread.php?threadid=567128 Maybe... I...

Currency #3
Dear expert, Whenever I key in the balance in the account list, eg , $123.44, it will automatically change to a round up figure like $123. How do i retain the decimal cents ? Thanks Weisheng In microsoft.public.money, <anonymous@discussions.microsoft.com> wrote: >Whenever I key in the balance in the account list, eg , >$123.44, it will automatically change to a round up >figure like $123. How do i retain the decimal cents ? Did you find my response to your posting 12 hours ago to not work for you? ...

How do I increase the plot area over a number of pages?
I would like to have a chart that extends across a number of pages horizontally and vertically. I can split up the data values to fake it horizontally, but I don't see any way to do this vertically. Is there any way to do this? TIA You can do this with a chart embedded in a worksheet. Click the chart. Drag the handles in the horizontal and vertical direction as desired. [You may want to set the zoom to 50% or even 25% before doing the dragging.] Now, select the cells behind the chart. The easiest way to do this is select the cell at the top-left corner, then SHIFT+click the ...

Compare numbers in to raws
I'm using Excel 2003 Can anybody help with programming this: I have a column of numbers in F20:F33 I would like to check against an other colums of numbers in A20:A23. All numbers are phone numbers - no parentheses. If a number in F match a number in A, it should return a "Y" (for yes) in the adressfield to the right for this number (say G28). I tried to modify the IF COUNT IF in the thread shown in "Programming" on the 10.02.2010 without succes. The above ranges are arbitrary. The real ones are much much longer. Thank you in advance. use edit>FIN...

length of check number
I recently got a new set of paper checks that started at #50000. When I download the cleared check information for these, MS Money is truncating the check number - so 50001 became just 1. Is this a known issue? I contacted Wells Fargo, but they didn't know anything about it. I can edit the check number and change it to 50001, so it seems to be able to hold that number of digits. Any pointers/suggestions would be greatly apprecaiated. Henry I'm betting if you checked the downloaded data, Wells Fargo is truncating it not Money. "Henry Winkler" <Henry Winkler@di...

How to extract the Number from a String
Hello friends I have a column with following type of data: 1243 no. of Books 213 no. of Pens What i want is to extract only the numeric data from the column and keeping the the original string as it is thanks in advance johnbest -- johnbest ------------------------------------------------------------------------ johnbest's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29694 View this thread: http://www.excelforum.com/showthread.php?threadid=494535 Hi John Assuming your data always has a space after the last numeric value, and with the data in A1 =--LEF...

Turn off Automatic Check numbers?
Does anyone know how to turn off the Automatic Check numbering within a checking account? This is particularly annoying when we put in a new withdrawal for a ATM Cash withdrawal or direct debit and a check number comes up. ...