Changing the format of a field dynamically.

[DCrake]

I am building an app that will be used in sereral different countries and each country has different currencies.

What I want to do is to change the format of any numeric fields in a report or on a form according to the site loading the app.

For example when using the app in Dubai Iwant it to be "AED "#,##0.000;"-AED "#,##0.000
And in Austrailia "ASD "#,##0.00;"-ASD "#,##0.00

It appears there is no intellisense to apply the format dynamically. Has anyone any ideas?

 

[gemma-the-husky]

if you use currency format, for currency fields - will they not just pick up the regional setting.

or do you mean have multiple currencies avaialble within the same program?

 

[DCrake]

The one app will be delpoyed worldwide and I want it to display the correct format irrespective of the location. I am aware of the reginonal settings aspect, just wanted to know if I could do it in the app.

 

[vbaInet]

Idea:

1. On opening the db, you check the settings/country - maybe by prompting the user
2. For each form you append the Format property for each textbox that requires this format.
3. To append, I think the form must be loaded, design view would do.
4. Save the form, then close.

This just needs to be done once.

 

[Avelino]

Hi
Another idea:
You can use the TAG property
Enter the property tag: AED #,##0.000;-AED #,##0.000?ASD #,##0.00;-ASD #,##0.00


The event "load"

Private Sub Form_Load ()
Dim j
j = Split (Me!tx2.Tag, "?")
Me!Tx2.Format = j(0)
End Sub

j(0) - Dubai
j(1) - Australia

Now, assuming you have a table language (tblLanguage) with the field IDlanguage.

thereby:

Me!Tx2.Format = j (DLookup("Idlanguage","tbllanguage"))

Success

 

[ChrisO]

[color=green]' I HATE FORMAT STRINGS SO I'M GUESSING (with a little testing)[/color]
Private Sub Detail_Format(ByRef intCancel As Integer, _
                          ByRef intFormatCount As Integer)
                          
    Dim strSavedFormatString As String
    Dim strFormat            As String
    
    Const conQuotes As String = """"""
    
    strSavedFormatString = "\A\E\D \$ #,##0.00; -\A\E\D \$ #,##0.00; ;Null Value"
    strFormat = conQuotes & strSavedFormatString & conQuotes
    Me.txtSomeDubaiNumber.Format = strFormat
    
    
    strSavedFormatString = "\A\S\D \$ #,##0.00; -\A\S\D \$ #,##0.00; ;Null Value"
    strFormat = conQuotes & strSavedFormatString & conQuotes
    Me.txtSomeAustrailianNumber.Format = strFormat
    
End Sub

 

[Galaxiom]

For the uninitiated curious about ChrisO's technique.

Const conQuotes As String = """"""
This line sets it as a string consisting of two double quote marks by using double quote to escape to a literal double quote.

When concatenated with strSavedFormatString further down it is treated as a literal double quote.

Another way to enter the format string that demonstrates the use of a double quote literal escape.
In this the AUD is in double quotes, once again escaped by a double quote character. It has the same effect as the literal designation of each letter with a back slash as shown by Chris.

Me.controlname.Format = """AUD ""#,##0.00;""-AUD ""#,##0.00[Red];Zero Value;Null Value"

Colour designation is also quite useful as I have shown for the negative value.

BTW Australian currency designator is normally AUD

(@ ChrisO. Note the currency character is not included in this currency format style.)

 

[ChrisO]

>>(@ ChrisO. Note the currency character is not included in this currency format style.)<<

It was included because it’s easier to remove if not required.
Similarly, I removed the third digit after the decimal separator.
Also, I added the Null Value but it too can be removed if not required.
ASD was forced to \A\S\D because ASD by itself causes problems but AED does not hence both were forced for compatibility.

 

[DCrake]

Re my original post, I made the big mistake of testing for .Format on a non numeric control. Now that I know there is a .Format property I think that will suffice.

There is a db settings screen in which the user identifies their location and by using this I can set a public variable to the relevant currency mask. This can then be used wherver needed in my app. Thanks for all the help guys.