Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to replace a number value to 0? 1

Status
Not open for further replies.

sommererdbeere

Programmer
Nov 20, 2003
112
US
hi,

my question here is:

i want to replace a number to 0. in formular editior, i have my code written as

if (workcen = 'su' and SAP <> 0) then
qty = 0 and
issued = 0
else
workcen

from the above, i want to show every &quot;SAP&quot; that is not equal to 0 whenever workcen = 'su'. if this is valid, then, replace everything in qty and issued column to be 0. otherwise, return all workcen.

example

data before changes made:
work cen qty issued sap
su 0 3 0
su 30 4 15
su 20 5 0
hk 30 0 40
hk 30 2 0

after the changes:
work cen qty issued sap
su 30 0 0
su 20 5 0
hk 30 0 40
hk 30 2 0


i've been posting for a while, but i didn't get respond .. i really really need your help.. please help and be advice.. many many million thanks..

m
 
hi m,

I'm not sure if my assumption is correct but to me it seems as if your trying to edit the data within the db? If so then that's not possible using crystal reports. Crystal only pulls data from a db and can't make changes to the db.

If that's not the case and you just want to show the data as you stated then your on the right track. You'll need to create mutliple formula's 1 for each field i.e. work, qty, issued, sap.

For the 2nd part you can use select expert to remove the records that show su that has 0's.
 
hi kphu,


can you show me an example or use my example as to how to show 'su' that doesn't contain 0's?

i just want to '0' out the column or qty and issued.. like a replace function?

many many thanks for your help..

m
 
i'm using crystal 8.5

if anyone knows the answer, please help.. please?? many many many thanks..

m
 
ok the first part

work cen qty issued sap
su 0 3 0
su 30 4 15
su 20 5 0
hk 30 0 40
hk 30 2 0

I'm assuming the workcen, qty, issued, and sap are fields.

Create a formula and name it frmqty

type in the following

whilereadingrecords;
integervar frmqty;
if workcen = &quot;su&quot; and SAP <> &quot;0&quot; then
frmqty := 0
else frmqty := qty;

in your report replace the field qty with the formula you created frmqty.

this will change the values of qty to show as &quot;0&quot;

Create another formula name this as frmissued

type in the following

whilereadingrecords;
integervar frmissued;
if workcen = &quot;su&quot; and SAP <> &quot;0&quot; then
frmissued := 0
else frmissued := issued;


in your report replace the field issued with the formula you created frmissued.

 
Thanks GmcNamara,

I working on crystal 8.5 as well and haven't moved on to the newer version. That's good to know.
 
hi kphu,


i want the outcome to look like:

after the changes:
work cen qty issued sap
su 30 0 0
su 20 5 0
hk 30 0 40
hk 30 2 0


please be advice.. many many thanks..

m
 
second part.

Go to select expert by either clicking on the icon that looks like a hand dropping 2 balls or by go to reports and scroll down to select expert.

click onthe show formula button.

click on the formula editor button.

type in
workcen <> &quot;su&quot; and
qty} <> 0

hope that helps.

Note that you may have to change the names of the fields so that its exactly to your report.
 
hi kphu,

in crystal, i wrote the code in formular editor:

if (workcen = 'su' and SAP <> 0) then
qty = 0 and
issued = 0
else
workcen

i try to use:
if (workcen = 'su' and SAP <> 0) then
replace(cstr(qty),cstr(qty), '0')
else
workcen

but still doesn't work...
i want replace all the value of qty to 0

do you have any other suggestions? many many thanks

m
 
when i try to use := , it said the remaining text does not appear to be in the formular
 
Hi, OK lets try this:

2 Formulas:

@NewQty
if ({workcen} = 'su' and {SAP} <> 0) then
0 else {qty}


@NewIssued
if ({workcen} = 'su' and {SAP} <> 0) then
0 else {issued}

Place these 2 in your report instead of the actual field names.Then for any su that has an SAP <> 0 the 2 fields will show as 0.

This assumes that qty and issued have numeric values from the database

[profile]
 

sorry I had to made some assumptions about the field properties.

I originally declared the new field as integer, you can try numbervar instead

so replace the integervar with numbervar
 
hi turkbear,

but i want to show work_cen even though it is no 'su'

so, how would i go about ur formular?

 
hi kphu,

if i do the follow,

whilereadingrecords;
numbervar = frmissued;
if workcen = &quot;su&quot; and SAP <> &quot;0&quot; then
frmissued := 0
else frmissued := issued;

is frmissued an new column that i created?

many many thanks to your response..

m
 
hi kphu,

it seems like it doesn't take := , because it keeps saying that this is not part of the formular

qty is a table, so is that why := doesn't take it?

table_qty := 0
 
where it says numbervar = frmissued;

take the = out so that it looks like this

numbervar frmissued

frmissued is a formula field that is created. Its basically the same data as the issued field except it shows the value of 0 where the conditions you set are true.
 
if qty is a table then you get an error as well.

qty should be a field within the table.

on my last response I forgot to include the ; at the end that needs to be in there

numbervar frmissued;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top