value to words not using towords 1

[aker]

Hi. I'm using CR 8.0 English version, and need to convert a value into portuguese, for example
3,076.79 becomes
Mil novecentos e trinta Euros e trinta e cinco Centimos

Anybody a formula out there that can help?

Thanks in advance.
Alan

 

[paulosinuk]

Don't think that it is possible. I had a similar issue and evenutally settled for a small table linking on country and containing the countries currency symbol. I could then atleast indicate the local currency by using the formula option on the currency symbol format of the field.

 

[Naith]

Could you explain how 3,076.79 becomes "Mil novecentos e trinta Euros e trinta e cinco Centimos" in real terms?

Naith

 

[Naith]

D'oh, never mind. I didn't stop long enough to realise that you were saying three thousand and seventy six in Portuguese. As you can tell, my Portuguese is about as good as my Swahili.

ToWords is available in 8.0 - is there some reason why you can't use it?

Naith

 

[paulosinuk]

Hi Naith,

What I was explaining is how I had to settle for a numeric value becoming €200 by using a temp table to hold the currency symbol of the country and then linking this at a country level to your database.

I then right click on the numeric field and on the number tab select the formula icon next to the currency symbol. Once in formula mode simply select the database field that contains the currency symbol and suddenly you have a dynamic currency symbol that changes based upon the local currency.

It's a compromise, you won't get Crystal to convert into words of a different language but you can atleast represent the currency.

Paulos

 

[synapsevampire]

Naith: He has the English version.

You could write one very large formula to handle this, but it'll be time consuming...

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[aker]

Thanks for the replies. synapsevampire: time consuming for whome? Me or the computer. The latter I could live with. I still haven't gotten round to this as it is only once a month I need it, and in 5 to 10 days it will have to be ready. Apart from one long formula, I guess I could use little subreports for every word, but i don't think subreports can be made to be part of text fields - they are always independant? Other than that, what about using arrays? Come on guys, somebody out there must already have done the time consuming work.
Time is ticking......

Regards.

 

[aker]

Naith: your apologies go unfounded as you unbeknowingly were correct to question my conversion statement.
3,076.79 does NOT become
Mil novecentos e trinta Euros e trinta e cinco Centimos
but
Três Mil e setenta e seis Euros e setenta e nove Centimos.

Regards.

 

[Naith]

Paulos,

I'm sorry, but I'm still pondering how your example is connected to this conversion problem.

SynapseVampire,

I have the English version, too. And ToWords is available.

Naith

 

[Naith]

...I am using 8.5 though. I can't vouch for 8.

 

[Naith]

Scal,

If you don't have ToWords in 8.0, then it's a chunky formula for you, I'm afraid. When SynapseVampire was talking about a lot of time being eaten up, I think he was talking about for you rather than for Crystal.

Even so, it'll be big, but not very type heavy. Just a shedload of copy and pasting. Convert the number to text, and work at representing the number in lengths incrementing by 1.

Come back if you don't get my drift.

Naith

 

[aker]

synapsevampire, I've been noticing your responses to many other questions in this forum and your participation should not go unrewarded, but in my small request you are unusually silent although you are obviously following as the saga unfolds.
I'm relatively new to Crystal and not too sure where to go to tackle this problem. Does it boil down to a lot of if/then statements where I start dividing by 100,000 then 10,000 then 1,000 etc.., arrays and/or case statements?
A small pointer in the right direction may at least get the ball rolling on this one, because although it doesn't seem that anybody else has encountered this problem, someday somebody else undoubtedly will.
naith, I do get your drift, and I will endeavour to use your method rather than the divide scenario above, but just not sure how to go about it.
paulosink, I don't believe it's not possible; CR managed it with towords. Now how to write it again inside CR, that's the killer. Is there any method for calling a DLL or linking in another function written externally in VB ?

Please help me somebody.

Regards.

 

[Naith]

Aw shucks, I'm a sucker for folk that ask nice.

I don't recall if CR 8.0 English has loop and ubound functionality. If it does, you could use them for a shorter version of what will otherwise turn out to be a very long formula for you.

I'm going to get you rolling with a cut-and-paste montage of a formula which'll get you converting numbers up to 99,999.

Let's get started then; it's about to get spammy in here:

//@ToWords Start

WhilePrintingRecords;

StringVar Amount := ToText({YourCurrencyOrNumberField},0,'');
//If you want to take decimals into account, switch 0 for 2.
//You'll then need to tweak the following a little.
NumberVar AmountLength;
StringVar SmallChange;
NumberVar SmallChangeLength;
//Use copies of AmountLength = 2 sections, but start from '.'+1 to derive the SmallChange values.

Local StringVar One;
Local StringVar Two;
Local StringVar Three;
Local StringVar Four;
Local StringVar Five;

If Instr(Amount,'.') > 0
Then AmountLength := Length(Mid(Amount,1,Instr(Amount,'.')))
Else AmountLength := Length(Amount);

If Instr(Amount,'.') > 0
Then SmallChangeLength := Length(Mid(Amount,Instr(Amount,'.')+1))
Else SmallChangeLength := 0;
//Continue SmallChange here or in another formula to handle decimals.

If AmountLength = 1
Then
(Select Amount
Case '0' : One := 'Zero'
Case '1' : One := 'One'
Case '2' : One := 'Two'
Case '3' : One := 'Three'
Case '4' : One := 'Four'
Case '5' : One := 'Five'
Case '6' : One := 'Six'
Case '7' : One := 'Seven'
Case '8' : One := 'Eight'
Case '9' : One := 'Nine'
End);

If AmountLength = 2
Then
If Mid(Amount,1,1) = '1'
Then
(Select Amount
Case '10' : Two := Two + 'Ten'
Case '11' : Two := Two + 'Eleven'
Case '12' : Two := Two + 'Twelve'
Case '13' : Two := Two + 'Thirteen'
Case '14' : Two := Two + 'Fourteen'
Case '15' : Two := Two + 'Fifteen'
Case '16' : Two := Two + 'Sixteen'
Case '17' : Two := Two + 'Seventeen'
Case '18' : Two := Two + 'Eighteen'
Case '19' : Two := Two + 'Nineteen'
End)
Else
(Select Mid(Amount,1,1)
Case '2' : Two := Two + 'Twenty'
Case '3' : Two := Two + 'Thirty'
Case '4' : Two := Two + 'Forty'
Case '5' : Two := Two + 'Fifty'
Case '6' : Two := Two + 'Sixty'
Case '7' : Two := Two + 'Seventy'
Case '8' : Two := Two + 'Eighty'
Case '9' : Two := Two + 'Ninety'
End);

If AmountLength = 2 and Mid(Amount,1,1) <> '1'
Then
(Select Mid(Amount,2,1)
Case '0' : Two := Two
Case '1' : Two := Two + ' one'
Case '2' : Two := Two + ' two'
Case '3' : Two := Two + ' three'
Case '4' : Two := Two + ' four'
Case '5' : Two := Two + ' five'
Case '6' : Two := Two + ' six'
Case '7' : Two := Two + ' seven'
Case '8' : Two := Two + ' eight'
Case '9' : Two := Two + ' nine'
End);

If AmountLength = 3
Then
(Select Mid(Amount,1,1)
Case '1' : Three := Three + 'One'
Case '2' : Three := Three + 'Two'
Case '3' : Three := Three + 'Three'
Case '4' : Three := Three + 'Four'
Case '5' : Three := Three + 'Five'
Case '6' : Three := Three + 'Six'
Case '7' : Three := Three + 'Seven'
Case '8' : Three := Three + 'Eight'
Case '9' : Three := Three + 'Nine'
End);

If AmountLength = 3 and Mid(Amount,2,2) = '00'
Then Three := Three + ' hundred'
Else
If AmountLength = 3 and Mid(Amount,2,2) <> '00'
Then Three := Three + ' hundred and';

If AmountLength = 3
Then
If Mid(Amount,2,1) = '1'
Then
(Select Mid(Amount,2,2)
Case '10' : Three := Three + ' ten'
Case '11' : Three := Three + ' eleven'
Case '12' : Three := Three + ' twelve'
Case '13' : Three := Three + ' thirteen'
Case '14' : Three := Three + ' fourteen'
Case '15' : Three := Three + ' fifteen'
Case '16' : Three := Three + ' sixteen'
Case '17' : Three := Three + ' seventeen'
Case '18' : Three := Three + ' eighteen'
Case '19' : Three := Three + ' nineteen'
End)
Else
(Select Mid(Amount,2,1)
Case '2' : Three := Three + ' twenty'
Case '3' : Three := Three + ' thirty'
Case '4' : Three := Three + ' forty'
Case '5' : Three := Three + ' fifty'
Case '6' : Three := Three + ' sixty'
Case '7' : Three := Three + ' seventy'
Case '8' : Three := Three + ' eighty'
Case '9' : Three := Three + ' ninety'
End);

If AmountLength = 3 and Mid(Amount,2,1) <> '1'
Then
(Select Mid(Amount,3,1)
Case '0' : Three := Three
Case '1' : Three := Three + ' one'
Case '2' : Three := Three + ' two'
Case '3' : Three := Three + ' three'
Case '4' : Three := Three + ' four'
Case '5' : Three := Three + ' five'
Case '6' : Three := Three + ' six'
Case '7' : Three := Three + ' seven'
Case '8' : Three := Three + ' eight'
Case '9' : Three := Three + ' nine'
End);

If AmountLength = 4
Then
(Select Mid(Amount,1,1)
Case '1' : Four := Four + 'One'
Case '2' : Four := Four + 'Two'
Case '3' : Four := Four + 'Three'
Case '4' : Four := Four + 'Four'
Case '5' : Four := Four + 'Five'
Case '6' : Four := Four + 'Six'
Case '7' : Four := Four + 'Seven'
Case '8' : Four := Four + 'Eight'
Case '9' : Four := Four + 'Nine'
End);


If AmountLength = 4 and Mid(Amount,2,1) = '0'
Then Four := Four + ' thousand and'
Else
If AmountLength = 4
Then Four := Four + ' thousand,';

If AmountLength = 4
Then
(Select Mid(Amount,2,1)
Case '1' : Four := Four + ' one'
Case '2' : Four := Four + ' two'
Case '3' : Four := Four + ' three'
Case '4' : Four := Four + ' four'
Case '5' : Four := Four + ' five'
Case '6' : Four := Four + ' six'
Case '7' : Four := Four + ' seven'
Case '8' : Four := Four + ' eight'
Case '9' : Four := Four + ' nine'
End);

If AmountLength = 4 and Mid(Amount,2,1) = '0'
Then Four
Else
If AmountLength = 4 and Mid(Amount,3,2) = '00'
Then Four := Four + ' hundred'
Else
If AmountLength = 4 and Mid(Amount,3,2) <> '00'
Then Four := Four + ' hundred and';

If AmountLength = 4
Then
If Mid(Amount,3,1) = '1'
Then
(Select Mid(Amount,3,2)
Case '10' : Four := Four + ' and ten'
Case '11' : Four := Four + ' and eleven'
Case '12' : Four := Four + ' and twelve'
Case '13' : Four := Four + ' and thirteen'
Case '14' : Four := Four + ' and fourteen'
Case '15' : Four := Four + ' and fifteen'
Case '16' : Four := Four + ' and sixteen'
Case '17' : Four := Four + ' and seventeen'
Case '18' : Four := Four + ' and eighteen'
Case '19' : Four := Four + ' and nineteen'
End)
Else
(Select Mid(Amount,3,1)
Case '2' : Four := Four + ' twenty'
Case '3' : Four := Four + ' thirty'
Case '4' : Four := Four + ' forty'
Case '5' : Four := Four + ' fifty'
Case '6' : Four := Four + ' sixty'
Case '7' : Four := Four + ' seventy'
Case '8' : Four := Four + ' eighty'
Case '9' : Four := Four + ' ninety'
End);

If AmountLength = 4 and Mid(Amount,3,1) <> '1'
Then
(Select Mid(Amount,4,1)
Case '0' : Four := Four
Case '1' : Four := Four + ' one'
Case '2' : Four := Four + ' two'
Case '3' : Four := Four + ' three'
Case '4' : Four := Four + ' four'
Case '5' : Four := Four + ' five'
Case '6' : Four := Four + ' six'
Case '7' : Four := Four + ' seven'
Case '8' : Four := Four + ' eight'
Case '9' : Four := Four + ' nine'
End);


If AmountLength = 5
Then
(Select Mid(Amount,1,2)
Case '10' : Five := Five + 'Ten'
Case '11' : Five := Five + 'Eleven'
Case '12' : Five := Five + 'Twelve'
Case '13' : Five := Five + 'Thirteen'
Case '14' : Five := Five + 'Fourteen'
Case '15' : Five := Five + 'Fifteen'
Case '16' : Five := Five + 'Sixteen'
Case '17' : Five := Five + 'Seventeen'
Case '18' : Five := Five + 'Eighteen'
Case '19' : Five := Five + 'Nineteen'
End);


If AmountLength = 5 and Mid(Amount,3,1) = '0'
Then Five := Five + ' thousand and'
Else
If AmountLength = 5
Then Five := Five + ' thousand,';

If AmountLength = 5
Then
(Select Mid(Amount,3,1)
Case '1' : Five := Five + ' one'
Case '2' : Five := Five + ' two'
Case '3' : Five := Five + ' three'
Case '4' : Five := Five + ' four'
Case '5' : Five := Five + ' five'
Case '6' : Five := Five + ' six'
Case '7' : Five := Five + ' seven'
Case '8' : Five := Five + ' eight'
Case '9' : Five := Five + ' nine'
End);

If AmountLength = 5 and Mid(Amount,3,1) = '0'
Then Five
Else
If AmountLength = 5 and Mid(Amount,4,2) = '00'
Then Five := Five + ' hundred'
Else
If AmountLength = 5 and Mid(Amount,4,2) <> '00'
Then Five := Five + ' hundred and';

If AmountLength = 5
Then
If Mid(Amount,4,1) = '1'
Then
(Select Mid(Amount,4,2)
Case '10' : Five := Five + ' and ten'
Case '11' : Five := Five + ' and eleven'
Case '12' : Five := Five + ' and twelve'
Case '13' : Five := Five + ' and thirteen'
Case '14' : Five := Five + ' and fourteen'
Case '15' : Five := Five + ' and fifteen'
Case '16' : Five := Five + ' and sixteen'
Case '17' : Five := Five + ' and seventeen'
Case '18' : Five := Five + ' and eighteen'
Case '19' : Five := Five + ' and nineteen'
End)
Else
(Select Mid(Amount,4,1)
Case '2' : Five := Five + ' twenty'
Case '3' : Five := Five + ' thirty'
Case '4' : Five := Five + ' forty'
Case '5' : Five := Five + ' fifty'
Case '6' : Five := Five + ' sixty'
Case '7' : Five := Five + ' seventy'
Case '8' : Five := Five + ' eighty'
Case '9' : Five := Five + ' ninety'
End);

If AmountLength = 5 and Mid(Amount,4,1) <> '1'
Then
(Select Mid(Amount,5,1)
Case '0' : Five := Five
Case '1' : Five := Five + ' one'
Case '2' : Five := Five + ' two'
Case '3' : Five := Five + ' three'
Case '4' : Five := Five + ' four'
Case '5' : Five := Five + ' five'
Case '6' : Five := Five + ' six'
Case '7' : Five := Five + ' seven'
Case '8' : Five := Five + ' eight'
Case '9' : Five := Five + ' nine'
End);

If Length(Five) = 0 Then
If Length(Four) = 0 Then
If Length(Three) = 0 Then
If Length(Two) = 0
Then One
Else Two
Else Three
Else Four
Else Five;

//@ToWords End

etc, etc. You get the drift, I'm sure. Like I say, this example will get you up to 1 shy of 100,000. You can follow the same ideal to take you up to 6 and 7 figure numbers if it's likely your current data will take you up that far.

I'll leave the converting from English to Portuguese to you,
porque eu não falo o português muito bem...

Boa sorte,

Naith

 

[Naith]

Unfortunately, I just tried to put my money where my mouth is, and found that the advice I gave you for decimalisation representation is entirely incorrect.

The formula above makes 3076.79 &quot;Three thousand and seventy seven&quot;. For it to translate it as &quot;Three thousand and seventy six pounds and seventy nine pence&quot;, you need to alter the beginning of the formula above to:

//Start Change
WhilePrintingRecords;

Local StringVar Amount := ToText(Truncate({Orders.Order Amount}),0,'');
Local NumberVar AmountLength;
Local StringVar SmallChange;
Local NumberVar SmallChangeLength;
Local StringVar SmallChangeAmount := Mid(ToText({Orders.Order Amount},2,''),Instr(ToText({Orders.Order Amount},2,''),'.')+1);

Local StringVar One;
Local StringVar Two;
Local StringVar Three;
Local StringVar Four;
Local StringVar Five;

If Instr(Amount,'.') > 0
Then AmountLength := Length(Mid(Amount,1,Instr(Amount,'.')))
Else AmountLength := Length(Amount);

If Instr(ToText({Orders.Order Amount},2,''),'.') > 0
Then SmallChangeLength := Length(Mid(ToText({Orders.Order Amount},2,''),Instr(ToText({Orders.Order Amount},2,''),'.')+1))
Else SmallChangeLength := 0;

//End Change

The following converts the fractions, or pence, cents, or centimos. Put the following code at the end of your formula, overwriting the original 'If Length(Five)..' etc section.

//Start Insert
If Mid(ToText({Orders.Order Amount},2,''),Instr(ToText({Orders.Order Amount},2,''),'.')+1) = '00'
Then SmallChange := SmallChange
Else SmallChange := ' and ';

If SmallChangeLength <> 0
Then
If Mid(SmallChangeAmount,1,1) = '0'
Then
(Select SmallChangeAmount
Case '0' : SmallChange
Case '1' : SmallChange := 'one'
Case '2' : SmallChange := 'two'
Case '3' : SmallChange := 'three'
Case '4' : SmallChange := 'four'
Case '5' : SmallChange := 'five'
Case '6' : SmallChange := 'six'
Case '7' : SmallChange := 'seven'
Case '8' : SmallChange := 'eight'
Case '9' : SmallChange := 'nine'
End);

If SmallChangeLength <> 0
Then
If Mid(SmallChangeAmount,1,1) = '1'
Then
(Select SmallChangeAmount
Case '10' : SmallChange := 'ten'
Case '11' : SmallChange := 'eleven'
Case '12' : SmallChange := 'twelve'
Case '13' : SmallChange := 'thirteen'
Case '14' : SmallChange := 'fourteen'
Case '15' : SmallChange := 'fifteen'
Case '16' : SmallChange := 'sixteen'
Case '17' : SmallChange := 'seventeen'
Case '18' : SmallChange := 'eighteen'
Case '19' : SmallChange := 'nineteen'
End)
Else
(Select Mid(SmallChangeAmount,1,1)
Case '2' : SmallChange := SmallChange + 'twenty'
Case '3' : SmallChange := SmallChange + 'thirty'
Case '4' : SmallChange := SmallChange + 'forty'
Case '5' : SmallChange := SmallChange + 'fifty'
Case '6' : SmallChange := SmallChange + 'sixty'
Case '7' : SmallChange := SmallChange + 'seventy'
Case '8' : SmallChange := SmallChange + 'eighty'
Case '9' : SmallChange := SmallChange + 'ninety'
End);

If SmallChangeLength <> 0 and Mid(SmallChange,1,1) <> '1'
Then
(Select Mid(SmallChangeAmount,2,1)
Case '0' : SmallChange := SmallChange
Case '1' : SmallChange := SmallChange + ' one'
Case '2' : SmallChange := SmallChange + ' two'
Case '3' : SmallChange := SmallChange + ' three'
Case '4' : SmallChange := SmallChange + ' four'
Case '5' : SmallChange := SmallChange + ' five'
Case '6' : SmallChange := SmallChange + ' six'
Case '7' : SmallChange := SmallChange + ' seven'
Case '8' : SmallChange := SmallChange + ' eight'
Case '9' : SmallChange := SmallChange + ' nine'
End);

If Mid(ToText({Orders.Order Amount},2,''),Instr(ToText({Orders.Order Amount},2,''),'.')+1) = '00'
Then SmallChange := SmallChange
Else SmallChange := SmallChange + &quot; pence&quot;;

If Length(Five) = 0 Then
If Length(Four) = 0 Then
If Length(Three) = 0 Then
If Length(Two) = 0
Then One + &quot; pounds&quot; + SmallChange
Else Two + &quot; pounds&quot; + SmallChange
Else Three + &quot; pounds&quot; + SmallChange
Else Four + &quot; pounds&quot; + SmallChange
Else Five + &quot; pounds&quot; + SmallChange;

//End Insert

I apologise for the misinformation earlier.

Best wishes,

Naith

 

[aker]

Wow, naith, what can I say. You are going to get a 'Mark this post as a helpful/expert post' for this one. I will implement your function into a report and give it a whirl immediately. Anybody who takes up so much of their time to help others deserves an immediate thankyou, so, thankyou. I'll keep the forum posted.

Regards.

 

[Naith]

No big deal. Most of it's copy and pasting, so it doesn't take too long, like you'll find out if you need to extend it.

By the way, the SmallChange teen section is wrong too! It should be

Case '10' : SmallChange := SmallChange + 'ten'
...
not

Case '10' : SmallChange := 'ten'
...
I'm clearly not concentrating because I'm more concerned in my &quot;Hours Til The Weekend&quot; countdown.

Have a good one,

Naith