Currency formatted as 0.0000

[twgonder]

Upon searching, I see a lot of people have problems with currency and formatting. But I didn't see this one.
Maybe my friends across the pond have dealt with different currency formats?
I have a currency field that is always positive, always has four decimal places to show. No commas or currency symbols, just number, "." and 4 decimals.

I set the format to 0.0000 and Access, for some strange reason keeps changing the format back to currency (even when I'm not in the table design), which doesn't work for my app.
I tried general number, and that worked until I loaded it onto a client laptop that uses formatting for South America. All the "." changed to "," and the app stopped working. I can't change the user's regional settings to "." for the Excel sheets they have.

I also tried setting the format for currency in their laptop to 0.0000, and again for some strange reason, Access changes it to 00.000 after pressing enter, which again doesn't work.

What does it take to get Access to stop "helping" me "fix" the currency format I enter and need?

 

[LanaR]

At Table level, try setting your Data Type to Number, Field Size to Single, Format to 0.0000 and Decimal Places to 4

If you need to display a currency symbol, you can do that at Form level. You could even use the On Current event to change the symbol if you are dealing with multiple currencies

 

[CJ_London]

it is not an access issue, it is a windows regional settings issue and will apply to any windows application, not just access.

for some strange reason, Access changes it to 00.000 after pressing enter

That's because the , is treated as a thousand separator

not sure what you mean by 'I can't change the user's regional settings to "." for the Excel sheets they have.'

It's a bit ambiguous - do you mean they have changed regional settings for excel,? or they haven't?

Never investigated it but you could look at the Language option under File>Options - it will affect all office apps and I have no idea whether that also impacts other things like presentation of numbers.

 

[CJ_London]

At Table level, try setting your Data Type to Number, Field Size to Single, Format to 0.0000 and Decimal Places to 4

don't think that is the issue - the issue is the OP want's to use a . as a decimal marker regardless of the regional setting - i.e. he doesn't want it replaced with a , when that is the regional setting format.

 

[twgonder]

@all It needs to remain a currency with the 4 decimal places. The large numbers as the integer part are important.
I can't understand why Access is having such problems with a format that I want to use. I've tried a few different tricks, but nothing seems to work to either maintain the format or display as I request.
I can't change the regional settings on a few dozen client computers because they use the regional settings in all their other apps to show local currency. The immediate problem I'm hitting is SQL doesn't like having commas in the WHERE portion that uses currency numbers.

From what I can see, language options don't affect numbers.

 

[Cotswold]

Access can be irritating when it doesn't comply as you expect it should do. But it is consistent in how it behaves, which is always reassuring.

I can't see why your field needs to be a Currency. Why not use a Long and string handle the decimal or simply multiply your value by 0.0001? Alternatively, can you not use a Double, or Single?

 

[sonic8]

I can't change the regional settings on a few dozen client computers because they use the regional settings in all their other apps to show local currency. [...]
From what I can see, language options don't affect numbers.

Please note: You already observed, but apparently refuse to understand, numbers, including currencies, are formatted differently in different regions of this planet. What is formatted correctly as a currency for you, is not so for billions of other people.
Primarily for this reason you should not mess with the Windows Regional Settings on other peoples' computers. Nonetheless, the Regional Settings do contain regional specific number settings.
Here is a screenshot:

The immediate problem I'm hitting is SQL doesn't like having commas in the WHERE portion that uses currency numbers.

That is a defect in *your* code. If you are building SQL statements in your own VBA code, you must take care of formatting any literal values correctly. This is mandatory for dates and decimal numbers. You can use the str function to convert a number to a string with strictly using the dot as decimal separator.
If you are using parameters to pass values to a SQL statement the data access client library will take care of handling the number format correctly.

 

[CJ_London]

You need to modify your code to take account of regional settings - you are going to come across this situation many times - user input, presentation, data import/export to name a few.

Cotswold and Sonic have provided some suggestions

 

[GK in the UK]

Have you tried setting the format of your field to a custom format? You say you set it to 0.0000 but did you define the format correctly?

Like this: #,###.0000

So:
entry of 1 shows 1.0000
entry of 1.27 shows 1.2700
entry of 1.2345 shows 1.2345

Works for me where the table field data type is currency. Can't say if it will over-ride locality setting for the decimal point but the documentation suggests it does.