Get rid of the dang "$" in currency already!

[twgonder]

Okay, I searched and found several threads from Google on how to get rid of (or change) the $ in currency fields,


and for me it isn't working.

Mine: Latin laptop with Windows installed from Spanish preinstall media, but in English; Windows 10 21H2; Region: Colombia, Region format: Spanish (Colombia); Access 2021 in 2016 mode


The suggestion is to change the region data within windows.
Since the suggestion to put a space in the field for symbol didn't work, I tried the pulldown for euro, did apply and it still doesn't work.





Save the db and reopened, nada. Shut down Access and reloaded db, nada, closed session and restarted, nada
If I change the first day of week in windows settings, it acts immediately in the task bar calendar (click on time control).

Is there something new in Access (or a kick in the pants needed) that ignores the regional settings or overrides them? I can't find anything in the Access File>Options.

[Bob Fitz]

Try the following line of code in the forms OnCurrent event:

Me.[NameOfYourTextbox].Format = "Currency"

I suspect (from the screen shot in your post) that you are showing us a table. The solution suggested is for use with a form which is of course, a much better tool to use when editing/entering data.

[twgonder]

At this point, I just want Access to show currency without the $ everywhere. Like it's supposed to.

[Bob Fitz]

Then I think you'll need to reset the the format property everywhere that it's used.

[mike60smart]

Hi

Set the field as a Number and format as Standard

[twgonder]

Here is a good explanation of why to use currency: Field Types in Microsoft Access (eggerapps.at)

I went back into the design view and saw this:



Okay, so maybe some kind of update is need to change all the field formats that contain currency to the new setting?
How is that done?

[CJ_London]

personally I would not use the format property in a table at all (although I guess there may be a reason somewhere, but can't think of one, perhaps use of colour?). The reason is that tables are for storing data, not presentation. By using format you hide the actual underlying data. You might be hiding a time element in a date field for example - and in the case of your currency if the value was 10.1945, all you will be seeing is 10.19 - then spend the next 5 days trying to work out why your multiplication doesn't work as expected.

Keep format property for the final presentation on a form or report

[twgonder]

Here's what I found: Microsoft Access tips: Currency format (allenbrowne.com)

[twgonder]

personally I would not use the format property in a table at all (although I guess there may be a reason somewhere, but can't think of one, perhaps use of colour?). The reason is that tables are for storing data, not presentation. By using format you hide the actual underlying data. You might be hiding a time element in a date field for example - and in the case of your currency if the value was 10.1945, all you will be seeing is 10.19 - then spend the next 5 days trying to work out why your multiplication doesn't work as expected.

Keep format property for the final presentation on a form or report

I think I hear what you're saying and it makes sense for tables. So far, I'm just designing tables.
It seems the format property is there, so you can't avoid using it.
As a default it sets to "currency" format when creating a new file of currency type.
Are you saying to change every currency type field to have a format of "General Number"?

I'm using a currency table, and putting a format in there. For dollars I would use #,###.00 where as for pesos I will use #.### (I think that will work). Since they don't use fractions of a peso, it makes sense to dedicate the space to only display whole numbers. And they use "." and not "," for a thousand separator.



I'm guessing that when I run a form or report, I can change the format to the format I want from this table?

[CJ_London]

It seems the format property is there, so you can't avoid using it.
As a default it sets to "currency" format when creating a new file of currency type.
Are you saying to change every currency type field to have a format of "General Number"?

No- just remove the entry

I'm guessing that when I run a form or report, I can change the format to the format I want from this table?

should be able to - avoid using the format function if you can as that will convert a number to text - which a) will sort differently and b) will need work to convert it back to a number

edit: just checked - if no format property in the field properties then a currency will still default to the local currency format, so you will need to use a format such as #.0000 to display all 4 (potential) digits of the currency (#,##0.0000 for thousand separators)

[twgonder]

After a bit of testing with the attached data base, I wasn’t able to find a format for currency that does what I want. It makes no sense to put in a custom format for every currency field that I create in every table. And maybe form and report depending on what they do or don’t do with the table format as a default. And then change it all if I go to another currency type for a new client that has or doesn’t have decimals.


The null format still shows the “$”, no matter what the regional settings contain even though the thousands separator is properly shown. It does show 0 decimals if set to zero in the regional settings. Close but no cigar for the “$”.

The General format doesn’t show the “$”, but it shows decimals correctly, however without a thousand separator. Since some currencies don’t have decimals, this is a candidate, except it doesn’t follow the currency setting in the regional settings.

The Currency and Euro formats are useless because of the “$” or “€” and the “(n)”.

Why the Fixed format shows 2 decimals no matter what is in the regional settings is baffling. Thanks for not showing the “$”, but where is the thousands separator? Why stop at two decimals? And I don’t see anywhere that can be set differently.

The Standard format also avoids the “$”, and has the proper thousands separator. However, it shows decimals for whole numbers.

It is interesting to note that changing the Region settings, in my case to Spanish (Colombia) that the Currency and Euro formats change to “$#.##0,00;($#.##0,00)” and “€#.##0,00;(€#.##0,00)”. Too bad they have the currency symbol and decimals, despite the Regional settings. They change back to “Currency” and “Euro” if the Region setting is set back to English (USA).

Aggghhhhhhh Microsoft! Such lack of attention to detail and compliance to their own standards in a product 30 years old. Why not get rid of the Euro type and make a Currency that works properly based on the region settings? When the Region setting is changed, they take the time to change the formatting in all the Currency fields in the db.


What I wanted:
1) For currencies that use decimals, show up to two decimals (is there a country that goes beyond two decimals in their currency?), and show the correct thousands separator
2) For countries that don’t use decimals, don’t show the decimal value.
3) Don’t show the $, ₤, € or any other regional symbol, except in maybe a total.
Reasoning:
1) I hate seeing a report that has a column of $ and “.00” all the way down. I need that space to print useful numbers. Often very large numbers, into the billions (what can I say, it’s a commercial banking app, it’s not my money).
2) I want my app to work with the two systems of currency by just changing one or two simple parameters in Regional settings or maybe better, in a control record in the db.

I live in Colombia, and many business people from other countries have stores here, and need some modern apps as compared to some old COBOL program fudged to run on Windows. These people would often like to see reports in their currency with titles and column headings in their native language.
Here are the rules I would have to develop:
1) Only allow entry and storage in the currency fields in the native currency of where the application is hosted. This means that all fields/controls of Currency that could be displayed outside the designated currency, and adjusted for exchange rates, have to be disabled for input, lookie only!
2) Any country that doesn’t use decimals in their currency, like pesos, has to have any calculated currency field that is stored truncated to a whole number so there aren’t any decimals lying around to muck things up.

Anybody dealt with these issues?

dtCurrFormatTest.accdb

[CJ_London]

I hate seeing a report that has a column of $ and “.00” all the way down.

numbers have 4 format states -positive, negative, zero, null

you are using the first two in your currency columns, just add ;"" for the zero

€#,##0.00;(€#,##0.00);""

you haven't said what you want to see instead, but perhaps have a - instead

€#,##0.00;(€#,##0.00);"-"

and there is a limited range of colours so if you want negatives in red and positives in green

[green]€#,##0.00;[red](€#,##0.00);"-"

By the way, if using this technique suggest put a space after the positive format so the numbers line up

€#,##0.00 ;(€#,##0.00);"-"

Note the null format has its uses - you can display text or a number if you wish 'null', 'N/A', 0 or even an message 'Required', 'Please enter a value', 'not specified', etc

I want my app to work with the two systems of currency
Anybody dealt with these issues?

if you are planning to show multiple currencies on the same form/report, changing regional settings won't work

Personally I don't show the currency symbol as part of the number, depending on requirement I either include it in the label caption e.g. 'Sales $' or 'Sales ($)' or as a separate textbox. Reason is I don't like all those $ and £ symbols

These people would often like to see reports in their currency with titles and column headings in their native language.

translation is a fairly common topic. You will find a number of examples on this and other forums. There are a number of techniques depending on requirement. All involve parsing through form and report captions and fetching the required translation from google translate or similar. But if you know your translations you could do this manually

The caption can then be updated with the translation and saved - this creates a 'fixed' language version of the app
Alternatively for a more dynamic solution the caption and the translation can be saved to a table and when a form or report is opened the first thing it does is scan the captions and update with the translation - typically based on the language of the user captured when they log in either from a user table or based on the windows language.

the table typically takes one of two forms. either just the caption text and its translation, or additional fields to identify the specific form and label. I prefer the former as I don't want multiple translations of 'customer name' etc.

However translation involves more than just captions - if you are using the format per above for nulls, these also need translation, also various message and input boxes (and these might be dynamic) you might be using in code, perhaps the contents of combo or listbox value lists, etc

[twgonder]

numbers have 4 format states -positive, negative, zero, null

you are using the first two in your currency columns, just add ;"" for the zero

...€#,##0.00 ;(€#,##0.00);"-"

Note the null format has its uses - you can display text or a number if you wish 'null', 'N/A', 0 or even an message 'Required', 'Please enter a value', 'not specified', etc
...
translation is a fairly common topic. You will find a number of examples on this and other forums. There are a number of techniques depending on requirement. All involve parsing through form and report captions and fetching the required translation from google translate or similar. But if you know your translations you could do this manually
...
However translation involves more than just captions - if you are using the format per above for nulls, these also need translation, also various message and input boxes (and these might be dynamic) you might be using in code, perhaps the contents of combo or listbox value lists, etc

Thanks for all your ideas. Some 25 years ago I was considering all of this when I was looking to port a big application to Access.
I was really beating my head against the wall, based on the lack of experience the general community had in Access.
I don't know if Paul Getz is still around, but my ideas had him stumped way back then.
I've been reading posts on currency and translation, and yes, there are a lot of good ideas out there.

My plan is to ask the user at login what currency they want to use (later what language too), and then rip through the db looking for all objects in tables, queries, forms and reports that use a currency field and updating the format with one stored for the user and also to adjust min and max entered values (different for dollars vs pesos so to fit in columns) and not to allow decimals for currencies that don't use them. When the db is split, the tables will remain in the native development format, which will be a bit of a pain if one gets into table views. My plan is to present the user with a complete application that insulates them from all design elements.

At the moment, I'm working on a simple (for me anyways) testing app to allow a user to login and specify the currency. I'll post it here with how to questions when it gets further along.

[CJ_London]

and then rip through the db looking for all objects in tables, queries, forms and reports that use a currency field and updating the format with one stored for the user and also to adjust min and max entered values (different for dollars vs pesos so to fit in columns) and not to allow decimals for currencies that don't use them. When the db is split, the tables will remain in the native development format, which will be a bit of a pain if one gets into table views. My plan is to present the user with a complete application that insulates them from all design elements.

Some things to consider.

To make those rip through changes requires each form/report/whatever to be opened in design view, changes made then saved.
-this could take some time (perhaps a minute or so)
-means you have to provide a .accdb FE which is much less secure than a compiled .accde and means you can't insulates them from all design elements

Consider making these changes when a form/report is opened (even with a .accde) so I would create functions in a 'change' module, perhaps combined with a table and call this in the open event to update as required. I use this technique for my translation module and takes no discernible time to run.

Only other alternative is to have two versions of the FE, one for each language which will make maintenance a pain as you will need to do it twice.

users should not be able to get into table views - there is lots you can do to prevent this, starting off with the easiest of which is to hide the navigation window. You might also want to investigate access runtime - a free version of access which has some functionality removed (primarily a reduced ribbon, no navigation window and disabled right click menus). But only useful if your users do not have access installed. You can mimic the runtime environment by renaming the file type from .accdb or .accde to .accdr. This is so that you as a developer can test in the runtime environment.

[twgonder]

Some things to consider.

To make those rip through changes requires each form/report/whatever to be opened in design view, changes made then saved.
-this could take some time (perhaps a minute or so)
-means you have to provide a .accdb FE which is much less secure than a compiled .accde and means you can't insulates them from all design elements

Now this is the kind of help I came to this forum for. It could be weeks before I get a prototype running, and bang, I can't do what I want for the "must open in design view" issue. I didn't mind the 1 minute or so delay, since very few people are going to be changing the language or currency on a FE workstation. Mostly just bosses and me doing testing.

This would also be an issue for multi-language, as I thought to just go and set all the caption properties at the same time I was doing currency changes after the user accepts the initial logon form.

I have no idea what the code looks like to do this, but it seems painful to have to do it when a form opens, lots of code scattered around. And when fields change! Aggghhh!
I'm surprised there isn't a way to just cruise through objects even in a .accde (but then I'm still learning the Access behind the scenes way).
Instead of going into design view from VBA to change properties in a .accde, is there a way to open a form in hidden view and change the properties all in one big pass?