In a Calc field, how to get 6.1 as 6.10

[SteveGriff]

I have a calculation field like this

"40 at a price of

[andygaunt]

I take it this is just a display field and not used for any calculations.

Why not just use a set of merge fields to accomplish the same thing.

<<Qty>> at a price of

[SteveGriff]

Yeah, it ain't as easy as that

It's a bit too long to type why I have to have it that way, but hopefully I can find a way of doing what I want to do with what I got before changing things

So, firstly, I need to find a way of doing it in a calculation field first.

Cheers

Steve Griff

[RussBaker]

Steve Griff,

This calculation will address your problem and covers situations where the field "Price text" ends in either, $6 , $6. , $6.1 or $6.10 . It also covers the situation where there may be other "." in Price text, for example "40 of the version 8.4 Guitars at $10.2".

If(Right(Price text, 1)=".", Price text&"00",

If(Position(RightWords(Price text, 1), ".", 1, 1)=0,Price text&".00",

If(Position(RightWords(Price text, 1), ".", 1, 1)=Length(RightWords(Price text, 1))-1,Price text&"0",

Price text)))

Russ Baker

Canberra, Australia

[Chuck]

This might be a little simpler:

code:

---

NumToText( NumberField ) &

Case(

NumberField = Int( NumberField ),

".00",

NumberField * 10 = Int( NumberField * 10 ),

"0",

""

)

Chuck

[ March 06, 2002, 04:52 PM: Message edited by: Chuck ]

[RussBaker]

Chuck,

I promise to read up on case statements one day - but I can't make this work on a text field as per Steve's original post - especially if the text field contains another number (like my 40 version 8.4 guitars at $10.2 example).

Russ

[danjacoby]

Just throw a TextToNum statement in there, as in:

Int(TextToNum(NumberField))

Caveat: I haven't tested this; it just seems to me that it should work.

P.S. Chuck -- really elegant!

[Rigsby]

All along the right track for a good FMP solution, but not what the guy wants. Just substitute the thing: Add one or a series of substitute commands to change .1 to .10 i.e.

---------

Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute(

"40 at a price of

[Chuck]

danjacoby,

Thanks. There are few higher compliments when I write code than that it was elegant (except of course, that it worked ) . You're right. If the original data is a text field, then using TextToNum should work.

My calculation does assume that the data is stored someplace as a number. I was assuming that when Steve mentioned it was a text field he was referring to the calculation having a text result, but that the 6.1 in his example came from a number field. If this is the case, then replacing the number field he already has with the calc I have above should solve the problem.

Chuck

[ March 08, 2002, 12:14 AM: Message edited by: Chuck ]

[BobWeaver]

If the price is always at the very end of the text, then this should work:

PriceText & Choose(Min(3,Length(PriceText) - Position(PriceText, ".", 1, PatternCount(PriceText, "."))),

"00", "0", "",".00")