currency format

[Dave]

using access 2002 with currency fields displayed in report. they always use
the ($1.00) type format for negative balances even when i change the setting
in the control panel. is there some over riding format specifier for access
somewhere that i haven't found???

 

[Rick Brandt]

using access 2002 with currency fields displayed in report. they
always use the ($1.00) type format for negative balances even when i
change the setting in the control panel. is there some over riding
format specifier for access somewhere that i haven't found???

You can specify a format using the format property of each control. *Named*
format settings like "Short Date", Long Date", "Currency", etc., use Windows
regional settings. Explicit format strings like "-$#.00" are not affected by
the user's regional settings.

 

[Dirk Goldgar]

using access 2002 with currency fields displayed in report. they
always use the ($1.00) type format for negative balances even when i
change the setting in the control panel. is there some over riding
format specifier for access somewhere that i haven't found???

I'm curious as to whether this might work: after changing the regional
currency settings in Control Panel, go into Access and change the format
to something else -- say, general number -- then change it back to
Currency. You may have to change it in the report's design view as well
as the table. Then see if it picks up the revised format from your
regional settings.

 

[Dave]

I'm curious as to whether this might work: after changing the regional
currency settings in Control Panel, go into Access and change the format
to something else -- say, general number -- then change it back to
Currency. You may have to change it in the report's design view as well
as the table. Then see if it picks up the revised format from your
regional settings.

hmmmm, interesting effect. changed control panel setting to -$1.00 format
then went back in to access and report view (and form view) showed same ()
format... but when checking properties in design view the format was changed
to $#,##0.00;($#,##0.00), selecting 'currency' from the list changed it to
match the control panel setting. that is an improvement, at least i can go
back and change all the places where dollars show up and make them
the -$1.00 format. unfortunately i would really like it to track the
control panel settings as the help says it should... especially since this
app has many spots where dollars are displayed that would have to be
changed, and since it is being deployed on machines with the runtime engine
so the users won't be able to edit the fields even if they wanted to.

 

[Dirk Goldgar]

hmmmm, interesting effect. changed control panel setting to -$1.00
format then went back in to access and report view (and form view)
showed same () format... but when checking properties in design view
the format was changed to $#,##0.00;($#,##0.00), selecting 'currency'
from the list changed it to match the control panel setting. that is
an improvement, at least i can go back and change all the places
where dollars show up and make them
the -$1.00 format. unfortunately i would really like it to track the
control panel settings as the help says it should... especially since
this app has many spots where dollars are displayed that would have
to be changed, and since it is being deployed on machines with the
runtime engine so the users won't be able to edit the fields even if
they wanted to.

I haven't investigated this in detail, but I had heard that the currency
format behaved oddly with respect to the regional settings. It's just
an idea, but if you anticipate this being a problem, might you put code
in the Open event of the form or report that simply sets the Format
property of various controls to "Currency"?

 

[Dave]

I haven't investigated this in detail, but I had heard that the currency
format behaved oddly with respect to the regional settings. It's just
an idea, but if you anticipate this being a problem, might you put code
in the Open event of the form or report that simply sets the Format
property of various controls to "Currency"?

oh, now thats a cute way to do it. kind of ugly though at least in reports
(haven't checked forms yet but assume its the same.... it doesn't look like
the 'format' property has a runtime set function so i have to do it like
this:
Me.CashBalance.Properties("Format") = "Currency"
it does work though, kind of hate to have to do that for each field, but it
is a way around it for now.

 

[Dirk Goldgar]

.... it
doesn't look like the 'format' property has a runtime set function so
i have to do it like this:
Me.CashBalance.Properties("Format") = "Currency"

No, the fact that the Format property doesn't show in the intellisense
dropdown list is just because not all controls have that property. You
can still write this:

Me.CashBalance.Format = "Currency"

and it will work -- at least, it does for me.

it does work though, kind of hate to have to do that for each field,
but it is a way around it for now.

One possibility would be to set the Tag property of all the currency
controls, and loop through all the controls on the report, just setting
the Format property for the tagged controls. E.g.,

Dim ctl As Access.Control

For Each ctl in Me.Controls
' If the control's Tag property is set to "Currency"
' set its Format property to "Currency" also.
If ctl.Tag = "Currency" Then
ctl.Format = "Currency"
End If
Next ctl

 

[Dave]

No, the fact that the Format property doesn't show in the intellisense
dropdown list is just because not all controls have that property. You
can still write this:

Me.CashBalance.Format = "Currency"

and it will work -- at least, it does for me.


One possibility would be to set the Tag property of all the currency
controls, and loop through all the controls on the report, just setting
the Format property for the tagged controls. E.g.,

Dim ctl As Access.Control

For Each ctl in Me.Controls
' If the control's Tag property is set to "Currency"
' set its Format property to "Currency" also.
If ctl.Tag = "Currency" Then
ctl.Format = "Currency"
End If
Next ctl

thats ok, just doing the .format="Currency" is fine, in most of these forms
and reports there are only one or two cash fields, in one of them there are
4 which is the biggest single one with cash in it... is all working now so
users can have their () or $- or -$ or whatever other one they want i
guess..

 

[Dirk Goldgar]

You can specify a format using the format property of each control.
*Named* format settings like "Short Date", Long Date", "Currency",
etc., use Windows regional settings. Explicit format strings like
"-$#.00" are not affected by the user's regional settings.

The trouble is, Rick, that if you set your Format property to
"Currency", and then change your regional currency settings, or move the
database to another PC with different regional currency settings, the
Format property is changed to an explicit format string that matches the
regional currency settings at the time the property was set. It appears
that what is actually stored is the explicit format string, and it's
only displayed as "Currency" when your PC's regional currency settings
match the explicit string that was stored.

All this, I should say, is just the way I remember and understand it,
and may not be exactly correct. But Dave's problem is a real one.