Currency Symbol

[ryetee]

Hi I have several reports where the currency was always reported in dollars so for the fields in question I had a format of $#,###.00 which printed the dollar sign in front of the amount.

As the system has now become more sophisticated I need to print out a currency symbol depending upon the client. I have a currency table that links into the client so my query 'behind' the report holds the right symbol but how do I get it ito the report. I Know I can define a second field that will hold the symbol but to me this is a clumsy way of doing it. Is there anyway I can put the symbol in the control source for the data or into the format of the field or some other way???

Thanks in advance

 

[Old Man Devin]

I suspect that instead of having the control on the report be 'currency' format you can make it not try to fit any format to the data and just literally pull it through, then if your stuff is already in the right format hopefully it won't have changed anything.

Perhaps format the control as text? Although if you then want to do any maths to the number you're pulling through it will start getting tricky...!

 

[ryetee]

I suspect that instead of having the control on the report be 'currency' format you can make it not try to fit any format to the data and just literally pull it through, then if your stuff is already in the right format hopefully it won't have changed anything.

Perhaps format the control as text? Although if you then want to do any maths to the number you're pulling through it will start getting tricky...!

I don't want to do any maths on it - the field comes from a table defined as currency but I'm just interested in the number.
Let me explain further what I have
In the data control source I am picking up CLIENTCOSTS from a query which effectively is the same field from a table. The table is defined as currency. In the format of the property sheet I have $#,##0.00
So if the value of CLIENTCOSTS = 1200 I get $1,2000.00 displayed on the report. If I change the format to #,##0.00 I get 1,200.00 on the report.

Now what I want to display is the currency symbol of the Client, not the locale of where the report is being run. Now I hold this on file. I can add another control to the report that holds the symbol but then I'm going to get the following (let's suppose currency is £)
If CLIENTCOST = 1200 report will display £1,200.00 but if
If CLIENTCOST = 12 report will display £ 12.00 (note the spaces).
Can I in someway set the control to be "CURRENCYSYMBOL + CLIENTCOST")?

Or in the format something like "CURRENCYSYMBOL#,###.00!

 

[Old Man Devin]

The '&' symbol is used to join things from different sources together in a single field. So someting along the lines of [field] = [CurrencySymbol] & [Amount] where amount is just the number.

And and if you wanted a space between the symbol and the amount, then you can add in a space via a string like:
[field] = [Symbol] & " " & [Amount]
i.e. quotes around a space

 

[ryetee]

The '&' symbol is used to join things from different sources together in a single field. So someting along the lines of [field] = [CurrencySymbol] & [Amount] where amount is just the number.

And and if you wanted a space between the symbol and the amount, then you can add in a space via a string like:
[field] = [Symbol] & " " & [Amount]
i.e. quotes around a space

Think I tried this using + instead of & BUT THANKS Old Man Devin!!! That works to a fashion but I'm now losing my decimal points so I'm now getting £12 instead of £12.00
I'm going to have a play around with the format but any advice welcome!

Not sure I can do anything from the format of the property sheet as it expects a number and now of course I have a symbol and a number!!
Can I format the field in the control source such as
=[CurrencySymbol] & [Amount, format=#,###.00)]

 

[Old Man Devin]

Not sure I can do anything from the format of the property sheet as it expects a number and now of course I have a symbol and a number!!
Can I format the field in the control source such as
=[CurrencySymbol] & [Amount, format=#,###.00)]

I think you can do something like Format([Amount],##.00) if I remember correctly. Might need quotes around the format choice, like "'##.00". In fact I think you will need those quotes so try Format([Amount], "##.00").

 

[ryetee]

Think I tried this using + instead of & BUT THANKS Old Man Devin!!! That works to a fashion but I'm now losing my decimal points so I'm now getting £12 instead of £12.00
I'm going to have a play around with the format but any advice welcome!

Not sure I can do anything from the format of the property sheet as it expects a number and now of course I have a symbol and a number!!
Can I format the field in the control source such as
=[CurrencySymbol] & [Amount, format=#,###.00)]

Ran out of time yesterday but cheers again I'll play around with that.


Spotted a funny now!! I remember now that I had used actually used [CurrencySymbol] & [Amount] in the past but got an error message. So let me explain.

I have 3 fields on a report which come effective;y from a table (via a query). All fields are held as currency. When I applied your solution to the 1st field I got warning massage on the report telling me I'd forgotten the "=". I put it in and it worked except for loosing the .00.

However when applying the same logic to [CurrencySymbol] & [field2] (and field3) and include the "=" I get a warning telling me I have a circular reference and when I run the report I get #type instead of the correct figure. If I remove the "=" I get a warning telling me I need it but the correct figure comes out. I'm loathe to use the latter as there is obviously some problem. Any ideas on all of this?

 

[Old Man Devin]

The circular reference must mean that in some way the formula have made is referring to something that itself refers to the formula you are currently writing.

E.g.
[Result1] = [Symbol] & [Field2]
[Field2]= [Symbol] & [Result1]

To know Result1 I must know Field2, but to know Field2 I must know Result1 one; a paradox that creates the error in Access.

So perhaps the way to go to write out (on paper perhaps) exactly everything that the formulae calculating the results you are using refers to and go through them by hand, looking for cases where you end up in an infinite loop as above.

 

[ryetee]

The circular reference must mean that in some way the formula have made is referring to something that itself refers to the formula you are currently writing.

E.g.
[Result1] = [Symbol] & [Field2]
[Field2]= [Symbol] & [Result1]

To know Result1 I must know Field2, but to know Field2 I must know Result1 one; a paradox that creates the error in Access.

So perhaps the way to go to write out (on paper perhaps) exactly everything that the formulae calculating the results you are using refers to and go through them by hand, looking for cases where you end up in an infinite loop as above.

Yeah I know what a circular reference is but can't spot it at the moment!!
I'm taking a deeper look and will report back!!

 

[Old Man Devin]

Yeah I know what a circular reference is but can't spot it at the moment!!
I'm taking a deeper look and will report back!!

Ah okay, well I hope you find it quickly, these sorts of things can be a nightmare!

 

[ryetee]

Yeah I know what a circular reference is but can't spot it at the moment!!
I'm taking a deeper look and will report back!!

Seems that this has been caused by the name of the control being the same as the control source. Have now changed control name and error has gone!

Just need to work out why I can't format to include 2 decimal places!

SOLVED - should be Formatnumber([Amount],2)