Question How do I format a number based on currency used? (1 Viewer)

[DWayne]

Background
I have a personal financial database that keep track of my accounts in three different countries, the US, Korea, and Australia.

Goal
I want to be able to display a report (or subreport, rather) of my accounts for each currency.

Problem
Korean won (KRW) do not use the "#,##0.00" system, just "#,###" (no decimal places, so when looking at reports of holdings by currency, the won comes up with decimals.

Additionally, I cannot use a dollar sign in the number format, because it will appear in front of the Korean won figures as well.

Question
How do I conditionally change the number format?

Ideally, I would like the number format to exist at the table level, so that I can bring it up in both forms and reports.

Conclusions
I think this will require a macro; while I have no problem writing macros in Excel, Access VBA is new to me.

Any and all help/suggestions are greatly appreciated. Thanks!

 

[pbaldy]

I don't think it can be done at the table level. This is untested, but I think you could display it in forms/reports with something like:

=IIf(Currency = "KRW", Format(...), IIf(Currency = "USA", Format(...), Format(...)))

Putting in the appropriate formats of course.

 

[ByteMyzer]

Why not simply use:

Format([MyField], "Currency")

...and let MS Access format the value according to the User's Regional Windows Settings?

 

[DWayne]

Thanks guys.

pbaldy, your suggestion sounds as concise as any. The only real issue is that I'd end up having to do that for each query. I'd love to make that a standing condition that gets naturally implemented in the entire database.

As for the ([My Field],"Currency") suggestion, that would work only if this database was for individuals who had only accounts in the region where they were using the account. My situation is doubly not that, because I have Korean accounts that I'm keeping track of while (or whilst -- this IS a british forum, after all) in the US, but even worse, it has to keep track of both currencies under the same user.

Thanks again, pbaldy. I'll give that a shot, maybe see how I can get some form of generalized method to implement, and if I find anything else out, I'll follow up up here.

 

[pbaldy]

Please do follow up; I'm interested to hear how it can best be done. That method feels like a kludge, but I couldn't think of anything better. As an alternative to putting it in different queries, you could create a function that accepted the country and amount as inputs, and returned a formatted string. That would let you call it from anywhere.

 

[Simon_MT]

One should store all currency values as Single/Double numeric type and with each transaction hold the Country and Currency or just Currency.

The purpose the Country file ties down the currency to a country as transactions may not always be in the domestic currency. You have a currency field in this table tied into the Currency Table which holds a Field with how the currency is to be formatted, I know of three possible formats:

S Standard 9,999.00
I Integer 9,999
F Francophile 9.999,00 (is another type of currency presentation)

So when it comes to the [iif] you can refer to the Currency table for the formatting treatment rather than testing for each currency. So:

iif(Currency_Format="I",CLng(Value),Value)

The Currency numeric type should never be used in a multi-currency environment.

Simon
Simon

 

[ByteMyzer]

You may want to check out the following link from Trigeminal Software. With this Module you get function FormatCurrencyIntl, which formats the number in a Currency Format by specified Region. For example:

?FormatCurrencyInt(1234.567, , , , , 2057)
£1,234.57

 

[DWayne]

I really don't think this is working as I thought.

First, some clarification:

Paul, your multiple Currency options seemed too good to be true, and that's what it was. However, in all honesty, I'm not sure that's the approach I want to take. It's rather inelegant (no offense, but any NEW currencies will require that I go through all of the forms and change the formulas.

I'd like to do something along the lines of having a preset currency format in a table, based on currency code.

I have one table, CurrencyTypes, and I've added another field [curFormat]that lists currency formats for whichever currency code there is.

I tried to add an event, On After Update, to the purchase log form, that sets the textbox property [Amount].Format to [CurrencyTypes].[curFormat].

When I click on anything in my Payment Method listbox, it says that I tried to create an event on an object but the component does not have the property set for Automation operations.

The brain's just not working. Any help?

 

[pbaldy]

You're not going to hurt my feelings; I already said it was a kludge. I did mention putting it in a function you could call from anywhere though, which would keep your maintenance to one place. Did ByteMyzer's link help any?

I'm not clear on the code you're trying to use. Can you post a sample db?

 

[boblarson]

I would just create a function to display it.

 

[Simon_MT]

I tried two option, one to change the input mask - succeeded with Postcodes but not francophile currencies.

Postcodes take (can be multiple) input masks a from a table with the known populate the combi Example (you do need an intermediate step) and from the selected example did this:

Function ClientsPostcode_ExampleAfterUpdate()
'       Apply the appropriate Postcode input Mask from the Example Field then move to Postcode
    With CodeContextObject
        .[Postcode].InputMask = .[Example].Column(1)
        DoCmd.GoToControl "Postcode"
    End With
End Function

After bytemizers splendid effort this all seems a little crude but I could'nt use the above to doctor of the input mask on francophile currencies so to change the presentation of the thousand separator:

    with CodeContentObject
        If .[Exhibition Currency Flag] = "E" Or .[EAmount] < 1000 Then
            .[Amount] = CCur(.[EAmount])
        ElseIf .[EAmount] > 999999 Then
            .[Amount] = Format(.[EAmount], "0\.000\.000")
        Else
            .[Amount] = Format(.[EAmount], "\.000")
        End If
    End With

Simon

 

[DWayne]

ByteMizer, It sounds good, if it works and it includes Korean currency formats as well. Where would I find out about that? And where would I implement that code anyway? I claim no knowledge of VBA for Access, and I'm kind of new to Access anyway. I might need another push, but I will give it a try with what I know

Simon_MT, I'm sure what your saying makes sense to the normal person, but it is miles above what I know, so I may gain some form of intelligence into the deeper lair of Access/programs before I can grasp your point.

Thanks to you all for your continued search for answers. I will focus on this a good while tomorrow and update everyone as to what is happening. I will also formulate a sample database so you can clearly see what I'm working with.

 

[DWayne]

Hey there, guys, thanks again for all your help. I think to do what you all were suggesting required that I have a more advanced knowledge of Access, coding, and probably things my brain cannot comprehend at this point to understand your suggestions. I did come up with a general function in a VBA module that will call the function up to reformat fields and columns. The sad news is that I have to write in an event to call up this function in each element that I create (queries, forms, other bells and whistles I will add in there later) that returns a monetary value , but it certainly will make things look cleaner on the viewer's perspective.

Here it is:

Sub ConditionalCurrency(FormatType As String, CurrCode As String, Ctl As Control)
Dim CurUnit As String
 
' Name the Currency Unit first as containing the format type (from the Currency Types table, defined earlier) and the currency code (in this case, I went with a simpler three letter code, such as AUD, KRW, USD)
    CurUnit = FormatType & " " & CurrCode
 
' Format it to show up red if negative.
    Ctl.Format = CurUnit & ";(" & CurUnit & ")[Red]"
 
End Sub

I was looking for something that would utilize the convenience of the database, as well as allow me to create a huge bank of currency types to make what I'm working on much more universal. Anyhow, if you have any other suggestions or tips, I'm all ears. Thanks again!