REPLACE Statement

[tforr]

Hi, Can anyone tell me if I am using the wrong code for my replace statement?

REPLACE Salary WITH Salary + IIF(wtran.wr_trncde = "A001", WTRAN.wr_tempval + WTRAN.wr_permval, 0)
REPLACE Allowances WITH Allowances + IIF(wtran.wr_trncde = "A002"+"A007"+"A008"+"A012", WTRAN.wr_tempval + WTRAN.wr_permval, 0)

"A002"+"A007"+"A008"+"A012" is the part my program doesn't like.
"A002","A007","A008","A012" I have tried this way as well.

Can anyone help?

Kind regards,

Tom

 

[bobmpalmer]

Tom
Presumably these are choices "A002","A007","A008","A012"

In which case you can use IIF(wtran.wr_trncde$"A002A007A008A012",etc.etc or

IIF(INLIST(wtran.wr_trncde,"A002","A007","A008","A012"), etc etc

Bob Palmer

http://www.resisystems.co.uk

The most common solution is H2O!

 

[Mike Lewis]

Tom,

Syntactically, your code looks correct. However, you'll get an error if the data types don't match.

Presumably, Salary, Wr_permval and Wr_tempval are all numeric? Adn wr_trncode is a string that is 16 chars long?

Also, are you sure you want to test wr_trncode against "A002"+"A007"+"A008"+"A012", that is, to test it against the concatanation of those four codes? Seems more likely you would want to test it against any one of those values, in which case, instead of:

wtran.wr_trncde = "A002"+"A007"+"A008"+"A012"

you would want:

inlist(wtran.wr_trncde,"A002",A007","A008","A012")

If that's not the answer, perhaps you could tell us the error message you got in your original code.

Mike




Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[TheRambler]

Hi,

Try IIF(wtran.wr_trncde $ "A002-A007-A008-A012",...
or
IIF(OCCURS(wtran.wr_trncde,"A002-A007-A008-A012")>0,...
or
IIF(INLIST(wtran.wr_trncde,"A002","A007","A008","A012"),...

 

[tforr]

would the following code not work then?

REPLACE Salary WITH Salary + IIF(wtran.wr_trncde = "A001", WTRAN.wr_tempval + WTRAN.wr_permval, 0)

REPLACE Allowances WITH Allowances + IIF(wtran.wr_trncde = "A002"+"A007"+"A008"+"A012"+"A014", WTRAN.wr_tempval + WTRAN.wr_permval, 0)

REPLACE Overtime WITH Overtime + IIF(wtran.wr_trncde = "A015"+"A016"+"A017"+"A018", WTRAN.wr_tempval + WTRAN.wr_permval, 0)

REPLACE XmasBonus WITH XmasBonus + IIF(wtran.wr_trncde = "A004", WTRAN.wr_tempval + WTRAN.wr_permval, 0)

REPLACE DeereBonus WITH DeereBonus + IIF(wtran.wr_trncde = "A005", WTRAN.wr_tempval + WTRAN.wr_permval, 0)

REPLACE SMP WITH SMP + IIF(wtran.wr_trncde = "A003", WTRAN.wr_tempval + WTRAN.wr_permval, 0)

REPLACE MiscPayments WITH MiscPayments + IIF(wtran.wr_trncde = "A011"+"A013", WTRAN.wr_tempval + WTRAN.wr_permval, 0)

REPLACE Pension WITH Pension + IIF(wtran.wr_trncde = "D006"+"D007", WTRAN.wr_tempval + WTRAN.wr_permval, 0)

REPLACE AVC WITH AVC + IIF(wtran.wr_trncde = "D008", WTRAN.wr_tempval + WTRAN.wr_permval, 0)

REPLACE MiscDed WITH MiscDed + IIF(wtran.wr_trncde = "D005"+"D009"+"D010"+"D011"+"D012"+"D013"+"D015"+"D016"+"D014", WTRAN.wr_tempval + WTRAN.wr_permval, 0)
ENDSCAN

Thanks and kind reards,

Tom

 

[Mike Lewis]

Tom,

wtran.wr_trncde = "A002"+"A007"+"A008"+"A012"+"A014"

It comes down to this: Does wr_trncode contain a four-character string, like A002 (in which case, no, the above won't work). Or does it contain a 20-char string, like A002A007A008A012A014 -- because that's what you are comparing it to in your example.

My advice, as I said before, would be to use INLIST().

Mike

Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[tforr]

Hi Mike,

Let me explain a little more.

Dept
Employee Number
Employee Name
Salary - A001
Allowances (to include car/fuel A002, shift allowance A007, first aid
A008,
travel allowance A012 and fuel benefit A014)
Overtime A015, A016, A017, A018
Xmas Bonus A004
Deere Bonus A005
SMP A003
Misc Payments (to include tel add A011, service award A013 and non tax
payments)
Pension D006, D007
AVC D008
Misc Deductions (to include non tax D005, D009, D010, D011, D012, D013,
D015
and D016 and tax deds D014)
PAYE
NI Ee's
NI Er's

Does this help,

Cheers,

Tom

 

[Mike Lewis]

Tom,

How many of those codes (A002, etc) can one instance of the Allowances field contain? Or, put another way, what is the length of that field?

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[tforr]

I have set the field to 00000.00

Tom