trim field with multiple decimals

[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
so main part is itm 44 (gd0190)

 

[chainedtodesk]

so main part is itm 44 (gd0190) 1st level is 44.1 (which would be the child of gd0190) 44.1.1 (the child of gd0189)etc etc. i have created a count (reclvl) which gets me the child level. 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 look back for the parent match. {pnbr: Right([item],Len([item])-InStr([item],"."))} this is hundreds of records and its just not working 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. can this be done i just cant seem to get it to work. thanks for any suggestions and help

 

[chainedtodesk]

1st level is 44.1 (which would be the child of gd0190) 44.1.1 (the child of gd0189)etc etc. i have created a count (reclvl) which gets me the child level. 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 look back for the parent match. {pnbr: Right([item],Len([item])-InStr([item],"."))} this is hundreds of records and its just not working 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. can this be done i just cant seem to get it to work. thanks for any suggestions and help