Trim "+/-" off a qty 3

[ngcn]

Hi,

I m trying to do this for a few days already, but it don't seem to work, sigh!

For example : I have a field named Qty, it can store +/- before it, e.g. +/-200, now I m trying to do a calculation with it, but first I have to trim off the "+/-". How do I go about it ??

The following is my code that won't worked :

WhilePrintingRecords;
StringVar Qty:=""; NumberVar Total:=0;
Qty:=Instr{ToText(PODetails.Qty}),"+/-"
If (Qty =1) then
Qty:=mid(ToText({PODetails.Qty}),1,3);
else
Qty:=ToNumber({PODetails.Qty});

Total:=ToNumber(Qty) - ToNumber({GoodsReceipt.QtyRecv});

please help ! Thank u !

best regards,
ngcn

 

[Ngolem]

well as one "ng" to another let us see where you went wrong

WhilePrintingRecords;
StringVar Qty:="";
NumberVar Total:=0;
Qty:=Instr{ToText(PODetails.Qty}),"+/-"
If (Qty =1) then
Qty:=mid(ToText({PODetails.Qty}),1,3);
else
Qty:=ToNumber({PODetails.Qty});

Total:=ToNumber(Qty) - ToNumber({GoodsReceipt.QtyRecv});

Is {PODetails.Qty} a string??? I think so to have the +/- in front and the way you treat the value latre in the formula....Qty is a string and you are assigning it a numeric in this statement:

Qty:=Instr{ToText(PODetails.Qty}),"+/-"

so let's revamp the formula to be this

WhilePrintingRecords;
NumberVar Qty := 0;
NumberVar Total:=0;

if Instr{{PODetails.Qty},&quot;+/-&quot <> 0 then
Qty := tonumber(right({PODetails.Qty}),
length({PODetails.Qty})-3))
else
Qty := ToNumber({PODetails.Qty});

Total := Qty - ToNumber({GoodsReceipt.QtyRecv});

Total;

That should do it.
Jim
JimBroadbent@Hotmail.com

 

[synapsevampire]

Do you need a whileprintingrecords?

There may be other gotchas, such as nulls and whether or not you actually have a number in there, but this should work, and it will return a 0 if it doesn't have valid data:

if isnumeric({PODetails.Qty}) then

iif
(
Instr({PODetails.Qty},&quot;+/-&quot <> 0,

toNumber( right({PODetails.Qty},length({PODetails.Qty})-3)) - ToNumber({GoodsReceipt.QtyRecv}),

ToNumber({PODetails.Qty})-ToNumber({GoodsReceipt.QtyRecv})
)

Else
0

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[ngcn]

Hi ngolem/Jim,

Thanks for ur fast response. U r right, Qty is a string.

I have tried ur method, but it was not working. Error keeps pointing to Instr, saying &quot;Formula Parse Error - A number, currency amount, boolean, or string is expected here.&quot;, even after I change to Mid({PODetails.Qty}, &quot;+&quot.

Do u think that this is because CR4.6 does not support Mid & Instr coz they r not found in Formula's functions ? Yes, I m using CR4.6 that comes with VB6 cd.

Can u suggest any other method to do this ? Please don't tell me to upgrade, I m out of time already.

desperate,
ngcn

 

[Ngolem]

is it possible that the function &quot;Instr&quot; is not legal in CR 4.3...hmmm it is Instr that is the problem not the other functions.

Glad you mentioned it was CR 4.3...you should have mentioned that at first.


wait a minute...did you copy and paste this formula directly...or retype it since I see an error in the brackets...I think this formula should work in CR4.3

WhilePrintingRecords;
NumberVar Qty := 0;
NumberVar Total:=0;

if Instr({PODetails.Qty},&quot;+/-&quot <> 0 then
Qty := tonumber(right({PODetails.Qty}),
length({PODetails.Qty})-3))
else
Qty := ToNumber({PODetails.Qty});

Total := Qty - ToNumber({GoodsReceipt.QtyRecv});

Total;

it is really tough to tell what is a bracket &quot;()&quot; and what is a brace &quot;{}&quot; on my screen...

I think I may have had Instr{{PODetails.Qty},&quot;+/-&quot instead of Instr({PODetails.Qty},&quot;+/-&quot as it should be

this formula should work fine.

BTW - THe &quot;WhilePrintingRecords is necessary on all formulas that aren't need for grouping or summary functions...it tells Crystal when to do the operation and doesn't leave it to CR's imagination....usually it gets it right occassionally it doesn't in complicated reports...but when forced with WhilePrintingrecords...it always times it right

Jim
JimBroadbent@Hotmail.com

 

[ngcn]

Hi Jim,

I retype ur formula & I notice the (){} mistake. As I have told u, it is not the formula's problem, I suspect that it is the CR4.6 that does not support the Instr function.

Do u know any other way to trim &quot;+/-&quot; off a number ??

best regards,
ngcn

 

[Ngolem]

try this...the &quot;+/-&quot; will always be the first 3 characters if it exists


WhilePrintingRecords;
NumberVar Qty := 0;
NumberVar Total:=0;

if left({PODetails.Qty},3) = &quot;+/-&quot; then
Qty := tonumber(right({PODetails.Qty}),
length({PODetails.Qty})-3))
else
Qty := ToNumber({PODetails.Qty});

Total := Qty - ToNumber({GoodsReceipt.QtyRecv});

Total;

that should work

Jim
JimBroadbent@Hotmail.com

 

[ngcn]

Sorry to tell u that CR4.6 also does not recognise Left & Right functions.

What to do ????

Anyway, u r very helpful!!

best regards,
ngcn

 

[Ngolem]

I don't have CR 4.3....list all the string functions that are available Jim
JimBroadbent@Hotmail.com

 

[ngcn]

Hi ngolem,

The followings are the list of functions that's available in CR4.3:

Arithmetic:
Truncate(x)
Sum(fld)
Sum(fld, condFld)
Sum(fld, condFld, cond)
Maximum
minimum
Count
DistinctCount
.
.
.

String:
Length(x)
TrimLeft(x)
TrimRight(x)
ReplicateString(x, #copies)
UpperCase(x)
LowerCase(x)
NumericText(x)
ToNumber(x)
ToText(x)
ToText(x, #places)
ToWords(x)
ToWords(x, #places)

Others:
Previous(fld)
Next(fld)
IsNull(fld)
PreviousIsNull(fld)
NextIsNull(fld)

The rest are Array, Date, EvaluationTime, DateRanges.
Thank YOU !!! U R very helpful.

best regards,
ngcn

 

[Martin97]

Hi,
try this to extract the number;

QTY :={PODetails.Qty}[4 to lenght({PODetails.Qty})];

I dont know if this work in ver 4.5. I think it's just a shortcut to the mid function but, can't hurt to try

let us know if it work.

Martin

 

[Ngolem]

Aha!!! you are absolutely correct...ripped off in my opinion...not a very useful product without those functions

I searched their knowledge base and found this article

# c2001810

I quote a part of it for you

**************

Seagate Crystal Reports (SCR) version 4.5 and 4.6 that is shipped with Microsoft Visual Basic 5 and Visual Basic (VB) 6, has LIMITED support for User Defined Functions List (UFL) functions.
Whatever is installed with the SCR versions (when VB is installed), is all that is available.

Solution

These versions do NOT support additional UFL functions. Either these functions are included in UFL's that are not compatible with SCR 4.5 and 4.6, or they are built directly into the CRPE32.DLL. They are not available to simply add to the bundled VB versions of SCR.
To get additional UFL support, Seagate Crystal Reports MUST be upgraded to the full Professional versions 4.5, 5 or 6

The following functions are NOT INCLUDED in the VB versions mentioned above, and cannot be added to these versions with UFLs. Note that they are standard in versions 5 and 6 Pro.

Arithmetic:
Truncate (x, #places)

Strings:
Trim (str)
ToText (x,y)
ToText (x,y,z)
ToText (x,y,z,w)
ToText (x,y,z,w,q)
Space (x)
InStr (str1,str2)
InStr (start,str1,str2)
StrCmp (str1,str2)
StrCmp (str1,str2,compare)
Mid (str,start)
Mid (str,start,length)
Left (str,length)
Right (str,length)
Val (str)
Chr (x)
Asc (str

**************

you are hooped til you upgrade your crystal version

then ANY of those corrected formulas will work.

Sorry... JimBroadbent@Hotmail.com

Reward good advice with a star, it reinforces us and helps others find answers to their problems.

 

[ngcn]

Hi Martin,

Thank you, your formula works perfectly for Qty with &quot;+/-&quot; in front of them, but return 0 for Qty without &quot;+/-&quot;.

Now the question is how to detect if a Qty has &quot;+/-&quot; in front of them ??

best regards,
ngcn

 

[Ngolem]

This works??? yet Left and mid functions don't exist...amazing!!

QTY :={PODetails.Qty}[4 to lenght({PODetails.Qty})];

then try this formula

WhilePrintingRecords;
NumberVar Qty := 0;
NumberVar Total:=0;

if {PODetails.Qty}[1 to 3] = &quot;+/-&quot; then
Qty := tonumber({PODetails.Qty}[4 to lenght
({PODetails.Qty})])
else
Qty := ToNumber({PODetails.Qty});

Total := Qty - ToNumber({GoodsReceipt.QtyRecv});

Total;

This is too funny if it works JimBroadbent@Hotmail.com

 

[ngcn]

Ngolem,

Hooorah !!! It works finally.

Thanks for your formula. You are very helpful.


best regards,
ngcn

 

[Ngolem]

I'll be damned...a simple INSTR, LEFT, RIGHT function is not allowed but working with a string as an array is...

Well I think you should eventually upgrade to a CR 8.5 so that you have more tools available...Glad this worked for you though JimBroadbent@Hotmail.com

 

[ngcn]

ngolem,

We've been thinking of upgrade for a long time already, but our company haven't bought the software yet. I think they will, within this year, hopefully.


best regards,
ngcn