Page 1 of 1
use of SUBSTR
Posted: Fri Jul 15, 2005 4:21 pm
by malaize_(Guest)
Hi,____We have many product codes that end in ^PTD^ and I would like to be able to remove the ^PTD^ part of the code for a Calculated field I am trying to create. Is there a way to use the SUBSTR function when the product codes are different lengths?____I am using R&R 9.____thanks, malaize
=> RE: use of SUBSTR
Posted: Fri Jul 15, 2005 5:42 pm
by Henneman
Malaize,____There may be several ways to skin this cat, but I^d try using the STRREP function, which serves to replace a string within a string, and can also replace a string with nothing. So a function named ALTPARTNO=STRREP(PART_NO,"PTD","") would yield....____PART_NO ALTPARTNO__12345PTD 12345__65478 65478__X5364PTD X5364________
==> RE: use of SUBSTR
Posted: Mon Jul 18, 2005 10:53 am
by malaize_(Guest)
Hi Henneman and thanks for the response.____I haven^t tried your method yet because I ended up using the following method that someone showed me.____1) Create a calculated field called right3____ right((rtrim(number),3)____And then query for right3="PTD"____2) Create another calculated field called left that drops the last three characters, ____ left(NUMBER, len(rtrim(NUMBER))-3)____This method works but now I have a second dilemma. In my report after getting the new left calculated field I need to do a lookup to STOCK.dbf to find the QUANTITY of this calculated field.____Is there a way to accomplish this?____Thanks so much,____malaize__
===> RE: use of SUBSTR
Posted: Tue Jul 19, 2005 9:18 am
by Rick_W_Johnson_(Guest)
Assuming that STOCK.dbf is ordered(indexed) on the part number, you can use the CLOOKUP(Indexed on characters) or NLOOKUP(indexed on a numeric) function to find it. Both are in the HELP with an example. Note that you need full path names for all entries. for example:____clookup(calculated field, "quantity", "c:pathSTOCK", "C:pathstock Index") is a start. You need to fill in the complete names and paths.