How to replace a number value to 0? 1

[sommererdbeere]

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

 

[sommererdbeere]

i have this

frmissued_qty is the formula field i created

whilereadingrecords;
numbervar frmissued_qty;
if {table_work_cen} = &quot;SU&quot; and {table_sap} <> '0' then
{table_qty} := '0'
else frmissued_qty := {table_qty}

but it doesn't take it, it keeps saying that i have problems.

many thanks

m

 

[kphu]

sommererdbeere,

if this is an existing report then if you would send me your report then I can write the formula's in there for you and send it back to you. I won't be able to test out the report as I won't be able to connect to your database. Please make sure you save the data with the report before you send so that I don't get an errors. You can set this by going to file and then click on &quot;Save data with the report&quot; that should place a check mark next to it. After that click the save button and then send me a copy of the report.


Another option would be is for me to create a sample of what your trying to do using the access northwind sample database. You can test it as long as you have the northwind sample database on your pc. It comes with ms acccess.

my email address is kennethp@webmpt.com

 

[sommererdbeere]

hi kphu,

sorry, i don't have norhwind database...

i'm sorry that i cannot send you the report, because it is confidential.

i really really appreciate your help and i hope u can help me as well.. let me explain clearly on my question, because i'm afraid i confuse you earlier..


i want to zero out the columns whenever work_center = 'su' and coln.sap != 0. also, i want to show the 'su' work center which the coln.sap doesn't contain 0. otherwise, show all work_center..

below is the code that i modify, but it doesn't work when i put else afterwards. also, this code doesn't work as well when i write it in Formula Editor:

if {coln.work_center }='su' and {coln.sap }<> 0) then
numbervar qty:= {coln.QTY};
numbervar completed := {coln.COMPLETED};
numbervar incompleted := {@Incomplete};
qty := 0;
completed := 0;
incompleted := 0

the above code doesn't work, but if i put 'else', it doesn't work as well
else
{coln.work_center }


it will be great if you can advise me on that..

many many many thanks..

m

 

[herbsza]

sommererdbeere

original data
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

Go to the record selection editor and do the following
{qty} <> 0

In the report place the original fields in the detail section and they should look like this.

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

Now create two formulas

@reworkissued

If {work cen} = 'SU' and
{sap} <> 0 then
0
else
{issued}

@reworksap

If {work cen} = 'SU' and
{sap} <> 0 then
0
else
{sap}

Place the following two formulas in the detail section of the report next to the original fields.

work cen qty issued @reworkissued sap @reworkedsap
su 30 4 0 15 0
su 20 5 5 0 0
hk 30 0 0 40 40
hk 30 2 40 0 0

now you can remove the original fields issued and sap

work cen qty @reworkissued @reworkedsap
su 30 0 0
su 20 5 0
hk 30 0 40
hk 30 2 0

 

[sommererdbeere]

hi herbsza,

however, how can i display ONLY coln for SAP that doesn't contain 0. meaning that i want to display work_cen = 'su' and coln.sap <> 0 and display all other work_cen as well.

many many many thanks..

by the way, thank you for ur respond to my problem.

i'm working on it now

m

 

[herbsza]

sommererdbeere

I think what you want to do is suppress the value's of sap when they are zero.

in other words

work cen qty @reworkissued @reworkedsap
su 30 0 0
su 20 5 0
hk 30 0 40
hk 30 2 0

in the detail section right click on the reworksap formula
and select the format field option. Then select the common
tab. The first option will be to suppress, click on the button on the right hand side of suppress.

{@reworkedsap} = 0

save and close
This should then suppress any 0 values in the sap column

work cen qty @reworkissued @reworkedsap
su 30 0
su 20 5
hk 30 0 40
hk 30 2

 

[sommererdbeere]

can i write the code in Record Selection Formula?

i have:

({WORK_CEN}='SCRAP' and {coln.SAP} <> 0) and
Uppercase({WORK_CEN}) like uppercase(replace({?Work_Cen}, &quot;%&quot;, &quot;*&quot)

the above code works, however, it only show 'SCRAP' but not all work_cen

i wanna show all work_cen, but for work_cen = 'su', i only want to show everything where coln.sap <> 0

this is my last step of my report..

please please please.. help..

many many million thanks

m

 

[herbsza]

sommererdbeere

replace the AND with a OR

({WORK_CEN}='SCRAP' and {coln.SAP} <> 0) OR
({WORK_CEN}) like uppercase(replace({?Work_Cen}, &quot;%&quot;, &quot;*&quot)

try that

 

[sommererdbeere]

it returns everything if i have %, however, the work_cen = 'SCRAP' shows all sap. i only want sap != 0

for example, when i type, %, i will have all work_cen including SCRAP. however, i ONLY want the data of work_cen = SCRAP where coln.sap !=0


do u have any idea on how to appoarch?


please be advice..

many many many thanks..

m

 

[herbsza]

Sommererdbeere

A posible problem might be.

When selecting the parameters {?Work_Cen} do you select
'SCRAP'. If so avoid doing so as you have already hard coded it into your where clause.
The where clause does the first part
({WORK_CEN}='SCRAP' and {coln.SAP} <> 0))
but if you select 'SCRAP' in the parameter it will look for it again in the second part but with out the condition you want.
({WORK_CEN}) like uppercase(replace({?Work_Cen}, &quot;%&quot;, &quot;*&quot)
there by giving you all the 'SCRAP' data you do not want.

The where statement should work as is.

 

[sommererdbeere]

hi herbsza,


i didn't included ({WORK_CEN}='SCRAP' and {coln.SAP} <> 0))
in the where clause, because i want to do it in crystal report, besides, i want to select all work center as well when i input % as wildcard.

do you know if i can go by this using Group Formular Editor instead of Record Formular Editor?

i just want to show all work center, however, when it comes to the point that it reads work_cen = 'SCRAP', then, i want to c only the record where {coln.SAP} <> 0} .

do you have any idea on how to appoarch? like what the code is like?

many many thanks

 

[herbsza]

Sommererdbeere

I have been on leave.
Do you still need this problem solved ?

 

[sommererdbeere]

hi herbsza,


thanks for ur reply... this problem has been resolve.. really thanks for ur time and input 'coz u helped me alot.. i hope if i have any questions next time, you can give me some help.. ehehehehee.. thank u very much.. wish u all the best..

m

 

[rrichardet]

Please submit the solution so that we can all learn!

Thanks.