Get only numbers before decimal point 1

[namax]

I have created a numeric field(expense (N6.2)) to hold data that involves money both in dollars and cents(like: $234.70).However the requirement is to separate them into their respective fields,like "234" in another field and "70" in another field.

I already converted the numeric field(namely expense) into a character field (namely expenses) using the STR() function.

I created two fields,one to hold the "dollar" part and the other for the "cents" part.

And now I would like to get the dollar part and insert into the new field "dollar".here is the code:

replace dollar with substr(expenses,1,3)

It is working well for 3 digits dollar part, data like $367.80,$893.60 but not for 2 or 1 digits dollar part, data like $78.00,$1.45,$3.50 and so forth.

I am thinking of using the FOR loop so as the FOR loop moves through the string it stops when it encounters the decimal point and replaces the dollar field with the numbers that are before the decimal point.

Could anyone help me out with this problem or just give me some clue so I can work around it.

Thankyou in advance.

Yanx

 

[stirfry]

Yanx

The solution is quite simple. You use the INT function.
? INT(expense) will give the Dollar amount.
? expense - INT(expense) will give the Cents.

However I totally disagree with your desire to create a field each for them.
Could you explain why you wish to do this.
Are you displaying the info on a Form? a Grid? a Report? There is a much simpler way of doing this.

Andrew

 

[Olaf Doschke]

I totally agree with stirfry. Why would you lower the quality of your data. If you need dollars and cents seperately for any output, you can do that separation on the fly, you don't need extra fields to store these values, like he already mentionsed there is int(), there also is floor(), which differs in the negative numeric range, but Int() will fit better even there.

If you convert to string first, you can use getword() and specify "." or better SET("POINT") as the separator of the "words" instead of space.

Dollars = GetWordNum(Transform(Currencyfield),1,Set("POINT"))
Cents = GetWordNum(Transform(Currencyfield),2,Set("POINT"))

This will work internationally with the locale specific decimal point (this is not a . anywhere in the world) and with an currency symbol, which will be part of the "dollar" string.

To separate the currency symbol, use a secondary step on Dollars:


Dollars = GetWordNum(Transform(Currencyfield),1,Set("POINT"))
Cents = GetWordNum(Transform(Currencyfield),2,Set("POINT"))
Dollars = Strtran(Dollars,Set("Currency",1),"")

The Currency symbol (or Symbols, eg a three letter acronym) as removed from the Dollars string, if you want to display it seperatly you can display Set("Currency",1), Set("Currency") tells, if the currency symbol is displayed on the left or right side.

All the SET() values are locale specific, if you simply SET SYSFORMATS ON, which is the default anyway.

SO that's how you do things, if you want a application working internatinally with locale currency specifications. Not included is a conversion of currencies of course, but if your app starts with empty data tables this is how it can be used to give users their locale specific currency format.

Bye, Olaf.

 

[Olaf Doschke]

One more thing to add, to explain why I said " Why would you lower the quality of your data?"

You can compute with a currency field, eg compute qty*price, this is of course not possible with strings, therefore keep the currency field.

If you never thought of removing the currency field anyway and just want to store the dollar and cents values additionally I can only warn you that any redundancy leeds to incorrect values. There will be day X at which you detect mismatches in currency value vs dollar and cents. This doesn't needs to happen intentionally.

You don't need secondary fields, as you can always compute the seperate values from the single currency field in one expression, eg if the currency field is named "price" and you have a qantity in qty, you can select

SELECT ;
PADL(Strtran(GetWordNum(Transform(price*qty),1,Set("POINT")),Set("Currency",1),""),6) As Dollars, ;
GetWordNum(Transform(price*qty),2,Set("POINT")) As Cents;
FROM yourtable WHERE productid = X

Bye, Olaf.

 

[namax]

Thankyou all for help.The INT() function you have pointed out is very helpful in my situation.Thankyou once again for your support.

Yanx