Select data based on special characters not length

[chainedtodesk]

I have a table that will have several part numbers and children parts based on a code. (se example)


Item_____Part Number___RecLvl
44 GD0190 0
44.1 GD0189G01 1
44.1.1 GD0115G05 2
44.1.1.1 GD0114P01 3
44.1.1.2 HA0028P01 3
44.1.1.3 HA0028P03 3
44.1.1.4 HA0028P02 3


The main part is itm 44 (gd0190).
The 1st child level is 44.1 (so in a new filed I would show parent as gd0190)

The 2nd child level 44.1.1 (the child of gd0189) etc etc.

I have created a count (reclvl) which gets me the child level based on decimals in the record.

What i need now is to be able to program a query to look up the proper parent of each child, I tried to use the (left, mid, right) and the (instr) to get the portion of the item to refer back for the parent match.
{pnbr: Right([item],Len([item])-InStr([item],"."))}

There are hundreds of records to reference and cannot get it to work as i need.

In this example a reclvl 3 i would like to pull from that record the 2 decimal record 44.1.1 so i would retrieve gd0115 as parent. Is this possible? based on the fact that the length is never the same between the decimals is there a way to pull everything to the left of a count of decimal places? thanks

 

[PHV]

You don't need RecLvl but the Left and InStrRev functions.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886