UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Fix Decimal Figure And Format, Access 2010    
 
   
Alhakeem1977
post Aug 20 2017, 11:20 PM
Post#1



Posts: 64
Joined: 8-July 17



I've got an Ms Access entry form named [frmOpsRecords] it has two fields one called txtCurrency (Combobox) and the other one called txtAmount.
The bound to a table named [tblOpsRegister] amount field format as Standard (in this format I'm getting the commas and dots automatically) with Decimal Places 2

1. How can I set the decimal with two or three depends on currency name selected by the user, like if "USD" should be two decimal or if "KWD" should be three decimal and so on to the other currencies.
Is there an option in Access can solve this issue or have to create a simple VBA code / Function?

2. How can I get the amount like this 123,544.23 instead of this 123,544.00 when the user types the amount it got changed automatically.

Thanks in advance

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
BruceM
post Aug 21 2017, 07:11 AM
Post#2


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


I believe you will need to use a function to specify the currency format. I know of no built-in setting.

I would apply the format to query fields, or to controls bound to the currency fields, rather than setting or changing them at the table level.
QUOTE
How can I get the amount like this 123,544.23 instead of this 123,544.00 when the user types the amount it got changed automatically.

If the field is integer or long integer, it doesn't accept decimals, and will ignore them. However, that doesn't prevent you from formatting the value.
Go to the top of the page
 
Jeff B.
post Aug 21 2017, 08:10 AM
Post#3


UtterAccess VIP
Posts: 9,880
Joined: 30-April 10
From: Pacific NorthWet


Have you considered using the Currency data type?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
BruceM
post Aug 21 2017, 08:44 AM
Post#4


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


I should clarify what I wrote earlier. As Jeff mentioned, the currency data type can be put to good use. However, to display various formats at the same time will require some additional coding.

If the user is selecting the currency name from a combo or list box, one option is to have the format for that currency type as a hidden column in the combo box row source. Apply the value of the hidden column as the format.
Go to the top of the page
 
Alhakeem1977
post Aug 21 2017, 12:03 PM
Post#5



Posts: 64
Joined: 8-July 17



Thanks for your kind reply BruceM and Jeff B.
Yes it's clear to me dear BruceM the data type of currencies are list box, do you think is it better to be Combobox then I have to consider the hidden column? Or as I know if it's list box I have set my VBA code like this with double couts "USD" is that true?

Secondly
QUOTE
I would apply the format to query fields, or to controls bound to the currency fields, rather than setting or changing them at the table level

I prefer that too if I know what you mean exactly because I am new at Ms Access ( I did my controls in my forms instead of in the tables) but how can I do it to solve this issue?

Many thanks I hope one day I can return your kind help.

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
BruceM
post Aug 21 2017, 12:27 PM
Post#6


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


How does the user select the currency name? Can you post a few rows of sample data?

You can have a hidden column in either a combo box or a list box, by setting the column width to 0.

QUOTE
I did my controls in my forms instead of in the tables

That is the correct way to do it.
Go to the top of the page
 
Alhakeem1977
post Aug 21 2017, 01:20 PM
Post#7



Posts: 64
Joined: 8-July 17



QUOTE
How does the user select the currency name?

By a separate selected drop down box named: txtCurrency, then types the amount in a separate text box named: txtAmount.

QUOTE
Can you post a few rows of sample data?

How can I do that? Do you mean to send the database?
Or like this (USD) (123,123.12) , other entry (KWD) (123,123.120).

Cab I set VBA code like this? I know it's wrong but how can I do it simple?
CODE
Dim strCurr As String
strCurr = Me.txtCurrency
If me.txtCurrency = "USD" then me.decimalformat = 2
If me.txtCurrency = "KWD" then me.decimalformat = 3
End if
End Sub

Thanks a gain.

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
BruceM
post Aug 21 2017, 03:00 PM
Post#8


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


What is decimalformat?

Forget what I said about the combo box column. One way you could do this is to have a table like this:

CODE
tblCurrencyFormat
  CurrencyFormatID
  FormatName
  CurrencyFormat


The data may be like this:

CODE
CurrencyFormatID   FormatName   CurrencyFormat
        1             USD          \$0.00
        2             KWD          \$0.000


Use something like this as the Row Source for the combo box where the user selects the currency name:

SELECT CurrencyFormatID, FormatName FROM tblCurrencyFormat ORDER BY FormatName

Set the combo box Column Count to 2, the Column Widths to something like 0;2, and the Bound Column to 1.

When the user selects the currency name, the CurrencyFormatID is stored in a field (I will call it CName) in that record. Base the form on a query that includes a left join between the main table and tblCurrencyFormat. For the formatted currency, a calculated field:

FormattedCurrency: Format([CurrencyField],IIf([CName] Is Null,"0.00",[FormatName])

This is easier to do than to explain. If you are having trouble, create a copy of the database with just a few sample records in the table where the user enters the amount, and the tblCurrencyFormat table I described if you added it. No other tables needed. Remove or change any sensitive information, zip the file (right click >> Send To >> Compressed (zipped) folder), and attach it to a reply.
Go to the top of the page
 
Alhakeem1977
post Aug 22 2017, 01:34 AM
Post#9



Posts: 64
Joined: 8-July 17



Please find attached the database.
I am sorry to disturb you, dear.

Thanks in advance.
Attached File(s)
Attached File  Register_23AUG17.zip ( 63.39K )Number of downloads: 2
 

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
BruceM
post Aug 22 2017, 07:44 AM
Post#10


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


Attached is what I was trying to describe.

I made a few changes. The field named Currency in tblOpsRegister was text. I changed it to number (Long Integer), and bound the combo box to that field.

The form was set to Data Entry. Since this allows the user to see only the records entered during the current Access session, I changed it so I could test the data by editing existing records. You can change it back, of course, if it suits your needs.

I commented out some Before Update code for the same reason. It was preventing me from changing the current record, which I needed to do for testing purposes. Again, change it if you like.

I showed an alternative to using the Before Update code you used to prevent duplication. I set a unique index on the combination of three fields (table design view, Indexes), then used the form's Error event to handle the error that occurs if there is duplication. If you like the approach you will want a more detailed message, and whatever else is needed to help the user.

I changed the Amount field from Long Integer to Currency. Long Integer does not accept decimals.
Attached File(s)
Attached File  Register_23AUG17_2.zip ( 72.85K )Number of downloads: 1
 
Go to the top of the page
 
Alhakeem1977
post Aug 23 2017, 10:21 AM
Post#11



Posts: 64
Joined: 8-July 17



Dear BruceM,

compute.gif Very good thanks a lot, but my aim is to get the "Formatted Amount" to be populated in the (tblOpsRegister) along with FormatName "Currency Name" not the CurrencyFormatID "Currency ID".

To be more clear I want the user once he selects following Currencies:

1. If selects Currency "USD" then the formatted decimal places in the "Amount" field will be (.12) with two digits only.
2. If selects Currency "EUR" then the formatted decimal places in the "Amount" field will be (.12) with two digits only.

3. If selects Currency "KWD" then the formatted decimal places in the "Amount" field will be (.120) with three digits only.
4. If selects Currency "BHD" then the formatted decimal places in the "Amount" field will be (.120) with three digits only.

I am so sorry for that may I didn't clarify it in my first post.

Regards,
Al Hakeem 1977

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
BruceM
post Aug 23 2017, 02:43 PM
Post#12


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


Al Hakeem, it would be best not to store the formatted value in the table. Store the value as currency, and format it as needed for each record. If you store a formatted value I believe you will have to store it as text. If you then want to perform calculations you may first need to convert the text values into numbers. The same problem could occur with sorting.

If you decide you want to store the formatted value, in the After Update event of the control where the amount is entered, use the expression I showed to write the formatted value to a text field in the table. Again, don't plan to do math with those values.

Me.FormattedValue = Format([CurrencyField],Nz([CName],"0.00")

Note that I used the Nz function, which is a VBA function. In queries I prefer to use the "native" expressions such as IIf when possible.

You can add any records you wish to the CurrencyFormat table, with any format. I was just trying to show a way to have the number be displayed in the correct format. You need to decide how you are going to store the values. Once again, I suggest storing them as numbers.
Go to the top of the page
 
Alhakeem1977
post Aug 27 2017, 04:57 AM
Post#13



Posts: 64
Joined: 8-July 17



thanks.gif
Thanks a lot dear, I am not going to do any calculations for this field, it's just a matter of Log between two Departments only.

One more thing if I delete the currency field is it possible to do the amount field can accept two decimal Places like " 123,123.12" and three decimal places " 123,123.123" without change the field type as a number or currency and standard format (which applies the commas and dot automatically) depends on the user"s typing by the keyboard?

Thanks again for your kind attention.

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
BruceM
post Aug 28 2017, 06:36 AM
Post#14


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


QUOTE
I am not going to do any calculations for this field, it's just a matter of Log between two Departments only

The other thing to consider is sorting. Numbers will sort like this:

20.019
101.230
1400.750
123,123.123

With text:

101.23
123,123.123
1400.75
20.00
QUOTE
if I delete the currency field is it possible to do the amount field can accept two decimal Places like " 123,123.12" and three decimal places " 123,123.123" without change the field type

You can store up to four decimal places in a currency field. Formatting controls how the values are displayed. If the user types 123,123.123 and the format is two decimal places the value will be displayed as 123,123.12. 123,123.126 will be displayed as 123,123.13.

If the field is formatted as three decimal places 123,123.123 will be displayed as 123,123.123; 123,123.126 will be displayed as 123,123.126. 123,123.12 will be displayed as 123,123.120.

My suggestion was that the user would select the currency format for each record, and the value would be displayed according to that format. What I am asking is this: Does the database I attached give you the correct result? If so, what would you like it to do differently?
Go to the top of the page
 
Alhakeem1977
post Sep 2 2017, 04:12 AM
Post#15



Posts: 64
Joined: 8-July 17



QUOTE
What I am asking is this: Does the database I attached give you the correct result? If so, what would you like it to do differently?


Yes, the database you attached works perfect, but it was just an idea came up while I was working on it.

Thanks a lot.
thanks.gif

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 
BruceM
post Sep 5 2017, 06:27 AM
Post#16


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


OK. Good luck with the project!
Go to the top of the page
 
Alhakeem1977
post Sep 8 2017, 02:31 AM
Post#17



Posts: 64
Joined: 8-July 17



kisses.gif

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    10th December 2017 - 09:27 PM