Currency conversion

[Bald Eagle]

Using MS Access 2002 I have developed a database (within the UK) and all my
money fields are formatted as 'currency'. I am about to modify my database
and give it to another charity which operates in Ireland where the currency
is Euros rather that Sterling. Someone has suggested that if my database is
run on their computer it might automatically display these fields as Euros.
Is that likely? Or am I going to have to reformat all the currency fields as
Euros?

 

[Allen Browne]

No. Access was designed so that it will still show the values in your
currency, even if a user opens it in different country
http://allenbrowne.com/ser-44.html

 

[Bald Eagle]

Thanks Allen. Having read your webpage, that sounds pretty definite! I'll
just have to do it the hard way!

 

[Bald Eagle]

Thanks Allen. Having read your webpage, that sounds pretty definite! I'll
just have to do it the hard way!

 

[tedmi]

IT depends on what you mean by "automatically display these fields as Euros."
If you mean take a value of say, 50 and convert it to the equivalent in
Euros, then no. But if you intend to ship an empty DB to Ireland, then if the
system their has regional options configured properly, currency will be
displayed with

 

[Bald Eagle]

Yes. I want to ship my database structure to an Irish computer and hope that
my currency fields will be Euro fields when data is inputted in the empty
database.

Are you saying that that is likely to happen as a result of the default
regional settings on the Irish PC being set to Euro? I won't be able to give
this a try until next month but it would certainly save a lot of work.

 

[tedmi]

Oops, my mistake. Turns out that Access, unlike some other programs I've
used, does not automatically use the currency symbol for the configured
locale - you must indeed reformat each currency field. What the locale
setting does provide is that the local symbol is the first one in the format
pick list for the currency field.

Sorry for the confusion.

 

[Bald Eagle]

Thanks for the clarification. Is there any simple way - such as Find and
Replace - to identify all the currency fields in the database and change them
to Euro?

 

[Allen Browne]

Simplest thing is to set your regional settings to the client's, set them
all to Currency, and save the tables like that. You can then set your
regional settings back, and you will see that Access has changed it to the
euro instead of showing Currency any more.

 

[Bald Eagle]

This looks promising. Thanks again for the advice.

 

[tedmi]

When you change the locale, every currency field's format gets changed from
"Currency" to a custom format that includes the currency symbol of the
PREVIOUS locale. You have visit every currency field of every table to set
the format to the new locale's currency symbol. Major BUMMER!

 

[Allen Browne]

It is possible to loop through the TableDefs, loop through the Fields of the
TableDef to locate the numeric fields, and test the Format property (using
error recovery in case it does not exist.)

 

[Steve Jensen]

Allen, I have a function that loops through the properties of all the fields
in a given table; however, 'Format' is not one of the properties. I would
very much like to give the user the option of globally changing the format of
Currency fields to match their regional settings. It is easy if there is a
property one can set; but I can't find it.

This lists all the properties of currency fields in a given table:

Debug.Print "Properties of fields in " & _
.TableDefs(stTable).Name & " table:"
For Each fldLoop In .TableDefs(stTable).Fields
If fldLoop.type = 7 Then 'currency field
Debug.Print i & ". " & fldLoop.Name

For Each fldProp In fldLoop.Properties
On Error Resume Next

Debug.Print fldProp.Name & " - " & fldProp.Value

Next fldProp
i = i + 1
End If
Next fldLoop

Format is not one of the properties, and if it was, I don't know what value
to set for a format of Currency.

Can you help with this?

Thanks,
Steve

 

[Allen Browne]

Most objects in Access have only a limited number of properties, until you
set them. If you test a field that has nothing in its Format property, you
get error 3270. But if the field does have something in its Format property,
it works.

That's why the previous reply concluded with the comment:
(using error recovery in case it does not exist.)

So, you need a piece of code that creates the property if it does not exist,
and sets it if it does. SetPropertyDAO() is what I use. You can copy it from
here:
http://allenbrowne.com/AppPrintMgtCode.html#SetPropertyDAO

As to what to set it to: set it manually to what you want. Then read the
property, and use that string as the target.

There is an additional trap to watch out for with the Currency format if
your users could have different regional settings:
http://allenbrowne.com/ser-44.html

 

[Steve Jensen]

Thanks, Allen
I read your article and also found some code for assigning the property if
it does not exist. I agree that the way Access works with regard to currency
makes no sense unless you are sharing the database across regions and wish to
maintain the currency of the originating region, which is probably relatively
rare.

I have found that if you create the property per above or do not assign a
format at all with a currency field (blank out the Format property), it will
display per the regional currency settings in table view. If you blank out
the Format property on currency fields in form and report bound controls,
again it displays per regional settings.

This is great until you get to unbound controls (for displaying totals on
forms and reports). Then you have to set the format property to Currency in
code, which is a pain, but you have relatively fewer objects to maintain.

 

[Steve Jensen]

BTW, I said the Currency was type 7 earlier, which is incorrect. In DAO it
is 5 as you point out in your web article.

 

[mrodriguez]

Most objects in Access have only a limited number of properties, until you
set them. If you test a field that has nothing in its Format property, you
get error 3270. But if the field does have something in its Format property,
it works.

That's why the previous reply concluded with the comment:
(using error recovery in case it does not exist.)

So, you need a piece of code that creates the property if it does not exist,
and sets it if it does. SetPropertyDAO() is what I use. You can copy it from
here:
http://allenbrowne.com/AppPrintMgtCode.html#SetPropertyDAO

As to what to set it to: set it manually to what you want. Then read the
property, and use that string as the target.

There is an additional trap to watch out for with the Currency format if
your users could have different regional settings:
http://allenbrowne.com/ser-44.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.



HELP !!!!!!!!!!I am not a programmer, just recently went back to college, just purchased,and downloaded ultimate steal microsoft package....the very first homework assignment I turned in I'm geting dinged because the Format - under Field Properties won't give me currency--I was trying to build a database with a "Monthly fee" Field.....it shows currency as an option but on the dropdown, curency shows as an RM number..am not getting anywhere with tech support ---on phone or online....I.Ibought this laptop and the software seperate and now I can't even use it or get help .....can soemone give me a clue ...

Martha

 

[strive4peace]

hi Martha,

set the DATA TYPE to Currency instead of Number. If your data type is
number and you want to format as currency but do not see it on the list,
simply type it in...


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*