Val function returning negative figures... 2

[jkob]

Hi,

I need to extract the number portion from a text string where the numbers always start from character 31 in the string. This is an example of the string I am manipulating:-

"Special Damages Calculation - 2461.84 - J O Bloggs"

However, there are also characters after the numbers that I don't need but because the number could be any length I can't guarantee where they might start so this is the formula I am using at the moment ...

Val (mid ({diary.dy_action},31))

This works and extracts the figure but it displays the - that appears after the number before it when it prints out so this makes the report think that it's a negative number....

Has anyone got any suggestions please ..

 

[vidru]

If the field will always have a "-" before and after the number portion, this should do it:
[tt]
Val(Split({diary.dy_action},"-")[2])
[/tt]
You should also consider a redesign of that table...

-dave

 

[jkob]

Thanks Vidru, that's fantastic. It worked first time..

The table does need redesigning you're right and that's the next step, I just needed something to work for existing entries...

Thanks again, Jenny.

 

[jkob]

One last query, what if the number in the string actually is a negative number ?

 

[Thadeus]

I think this will work. You stated that the start of the number would always be at Character 31, so I took the liberty of using that static figure in my response.

This also assumes that the field only displays dollar amounts to two decimal places.

local numbervar posStart;
local numbervar posPeriod;
local numbervar lenEntry;
local numbervar dispEntry;

posStart := 31;

posPeriod := Instr(posStart, {table.field}, ".");
lenEntry := posPeriod - posStart;

dispEntry := ToNumber(Mid({table.field}, posStart, lenEntry + 3));
dispEntry;

 

[jkob]

Thanks a lot Thadeus .. that's worked perfectly. I no whave all my values displaying correctly whether they are negative or positive.

Thanks for your help, Jenny.