none
Euro to The pound sign (£) in Forms and Reports RRS feed

  • Question

  • Hi

    I got almost headaches when I wanted to change Currency symbol from SEK (kr) to Euro. But found a way since Euro is supported within MS Access. Now I want to change from Euro to The pound sign (£) in Forms and Reports.

    Whats the best way to do it?


    Cheers // Peter Forss Stockholm

    Tuesday, February 12, 2019 8:30 AM

Answers

  • I used the Format Property. Change from Euro to "£ "00,00

    Hi Peter,

    I think you know my preference for generalisation. This is a way to handle it.

    In a shared library (by all my applications) I have a table with two fields: currency type and format_string (including currency symbol and number of decimals). When I need to use currency formatting, then I select the appropriate currency type from this table, and assign the format string to a global variable glo_currency_str.

    In the Forms and Reports, always the glo_currency_str is used for the formatting. It is now easy to use a different currency in your forms or reports, just by replacing glo_currency_str with a different value. Any new currency type (for Denmark or Iceland or …) is also simple to handle, just by adding a new line to the general currency table.

    Imb.

    • Marked as answer by ForssPeterNova Wednesday, February 13, 2019 3:17 PM
    Wednesday, February 13, 2019 12:51 PM

All replies

  • There is no "smart" method. You can use Format:

    UKDisplay = Format([Amount], "£0.00")


    Gustav Brock

    Tuesday, February 12, 2019 9:34 AM
  • Hi Gustav

    Thank yoo for helping. You moved me in the right direction.

    I used the Format Property. Change from Euro to "£ "00,00

    This could be the "smart" method since I will use V-Tools and search and replace "Euro" to "£ "0 and DecimalPlaces Property 2
    It will find the more then 600 "places" in Forms and Reports.

    Link to download V-tools


    Cheers // Peter Forss Stockholm


    Tuesday, February 12, 2019 10:04 AM
  • Sounds like an excellent solution/method.

    Gustav Brock

    Tuesday, February 12, 2019 10:07 AM
  • I used the Format Property. Change from Euro to "£ "00,00

    Hi Peter,

    I think you know my preference for generalisation. This is a way to handle it.

    In a shared library (by all my applications) I have a table with two fields: currency type and format_string (including currency symbol and number of decimals). When I need to use currency formatting, then I select the appropriate currency type from this table, and assign the format string to a global variable glo_currency_str.

    In the Forms and Reports, always the glo_currency_str is used for the formatting. It is now easy to use a different currency in your forms or reports, just by replacing glo_currency_str with a different value. Any new currency type (for Denmark or Iceland or …) is also simple to handle, just by adding a new line to the general currency table.

    Imb.

    • Marked as answer by ForssPeterNova Wednesday, February 13, 2019 3:17 PM
    Wednesday, February 13, 2019 12:51 PM
  • Hi Imb

    Thank you very much. 

    The table is easy. Can you give me the code too?


    Cheers // Peter Forss Stockholm

    Wednesday, February 13, 2019 2:01 PM
  • The table is easy. Can you give me the code too?

    Hi Peter,

    Yes, the code is also easy, I have digged it out for you.

    To display an amount in a Form or Report, I use the function  In_curr:  = In_curr(Me!Amount)

    Function In_curr(cur_currency As Variant, Optional no_zero As Boolean) As String
      If (IsNull(cur_currency)) Then Exit Function
      If (cur_currency = 0) And (no_zero) Then Exit Function
      
      In_curr = glo_valuta & "  " & Format(cur_currency, glo_currency_str)    'e.g. "###,##0.00"
    End Function
    

    glo_valuta  contains the currency symbol ("SEK", "€", …)

    glo_currency_str contains the the way the value is formatted. The 2 spaces between glo_valuta and the Format-function could also be incorporated in  glo_currency_str.

    Both  glo_valuta  and  glo_currency_str  are stored in the general table. Which values for these globals will be used can be asked at the beginning of the Form or Report, or can be retrieved from the table using some ID for that table, or from some default setting.

    You can add many more features, and you can argue whether the globals should be globals or parameters for this function. For all my general routines, and  In_curr  is such a one, I tend to use globals in a very controlled way: it speeds up programming, but also makes it easier to adept changes to the underlying systematics. But that is a matter of taste.

    Imb.

    Wednesday, February 13, 2019 7:26 PM
  • Hi Imb

    This how I want to do it. But I cant get it working:

    Having a table with the name “cur_currency”:

    cur_currency

    glo_valuta

    glo_currency_str

    Euro

    # ###,00" €";# ###,00[Röd]" €";0;"Undefined"[Röd]

    GBP

    "£ "# ###,00;"£ -"# ###,00[Röd];0;"Undefined"[Röd]

    SEK

    # ###,00" kr";# ###,00[Röd]" kr";0;"Undefined"[Röd]

    USD

    "$ "# ###.00;"$ -"# ###.00[Röd];0;"Undefined"[Röd]

    Having a Form with a control where the user can choose preferred Currency for the App:

    Another, hidden Form (the same as I need to maintain a persistent connection) “contains” the choosen currency string and it can be catch like this:

    CurrencyString = Forms![alla val]![AllaValCurrency].Column(1)

    Gives this by debug.print:

    "£ "# ###,00;"£ -"# ###,00[Röd];0;"Undefined"[Röd]

    So, I want to use the variable “CurrencyString” to format currency fields on Forms and Reports.

    But I can’t make it :-(


    Cheers // Peter Forss Stockholm

    Saturday, February 16, 2019 11:18 AM
  • You must use the English (non-localised) syntax, like:

    "£ "# ###.00;"£ -"# ###.00[Red];0;"Undefined"[Red]


    Gustav Brock

    Saturday, February 16, 2019 11:32 AM
  • Hi Gustav

    [Röd] to [Red] was not the issue.

    Its how to make CurrencyString  to a global variable that can be used for Format. Guess it s easy when you know it. But I am stuck.


    Cheers // Peter Forss Stockholm

    Saturday, February 16, 2019 12:10 PM
  • Also note the dot as the decimal separator.

    If you have CurrencyString, then just set the Format property to this:

    Me!SomeTextbox.Format = CurrencyString



    Gustav Brock


    Saturday, February 16, 2019 12:16 PM
  • Hi

    This is my declaration:

    Option Compare Database
    Global GBL_UserCurrency As String
    Option Explicit

    Public Sub Init_Globals()
    'Access global variable initialization
    GBL_UserCurrency = Forms![alla val]![AllaValCurrency].Column(1)
    End Sub

    This is my Call:

    Private Sub Form_Load()
    Call Init_Globals
    End Sub

    This is the debug.print GBL_UserCurrency:

    debug.print GBL_usercurrency
    "£ "# ###,00;"£ -"# ###,00[Red];0;"Undefined"[Red]

    But After Open of the Form where I want to use GBL_UserCurrency. debug.print is Null.

    This is the OnLoad Property:

    Private Sub Form_Load()
    'Call HideRibbon
    Call Fakturering_av_order_Rabatter2
    End Sub

    This is the OnCurrent Propert:

    Private Sub Form_Current()
    Call Fakturering_av_order_Rabatter2
    End Sub

    And "Fakturering_av_Order_Rabatter2" have Queries updating the Customers Prize, depending on discount or no discount.

    Some how, the Call Fakturering_av_order_Rabatter2 wipes out the Global Variable GBL_Usercurrency.

    Why? Please help.



    Cheers // Peter Forss Stockholm

    Saturday, February 16, 2019 3:17 PM
  • If function Fakturering_av_order_Rabatter2 clears the global variable, you will have to debug that function.

    Gustav Brock

    Saturday, February 16, 2019 3:29 PM
  • "£ "# ###,00;"£ -"# ###,00[Röd];0;"Undefined"[Röd]

    Hi Peter,

    Long time ago I also struggled with the above definition. As far as I remember it is an extended definition on how you want to display your currency. How it works I do not know, but I thought it is a system wide definition, and not useable as a variable, so I stopped with it, and started to use my function 'On_curr'.

    But now you can get it to work.

    Place the function In_curr in a general module, and declare two global variables: Public glo_valuta As String  and  Public glo_currency_str As String.

    Then you can assign these variables a values, e.g.  glo_valuta = "£", and glo_currency_str = "# ##0,00". If you now run a report whcih uses the In_curr function, then you should see the right formatting.

    Next step is to retrieve the values for the globals form the table "Currencies" (you can give thew globals, the table, and the fields in the table names that best suits with your house style). The field in your table for the currency_str can be split in two field: the valuta symbol and the formatting string.

    After the user selection of the currency (GBP) these two fields are assigned to the two globals.

    Finally you can tune the In_curr function. Depending on the currency, you want the valuta symbol before or after the value. This can easily be done with a SELECT CASE statement where the function result can be modified. Alse you can add functionality what to do with negative numbers, 0 values or Null values.

    I hope I am clear enough, and wish you success with the result.

    Imb.


    • Edited by Imb-hb Saturday, February 16, 2019 9:24 PM
    Saturday, February 16, 2019 9:22 PM
  • Hi Gustav

    Thank you.

    There was an unnecessary "end" in the function that caused the problems. Debugger did not find it. It has been there for 7 years without causing any "damage". It was only now in combination with the global variable that the problems arose.


    Cheers // Peter Forss Stockholm

    Sunday, February 17, 2019 9:07 AM