Parse field

[Guest]

Hi -

I am confused as to how to do this function in Access. I want to create a
table which is built from an existing table. The main difference I have is
to separate the data out in the PO_ITEM_NBR field.

Current
PO_ITEM_NBR Result PO_ITEM PO_NBR
123344 001 123344 001
123344 002 123344 002
123455 001 123455 001
etc.

This can be a permament change because the data will not be manuplated. I am
not sure where the best place to do this. The data will be displayed in a
form and used in a report.

Any and all help is appricated.

Thanks,

 

[Marshall Barton]

I am confused as to how to do this function in Access. I want to create a
table which is built from an existing table. The main difference I have is
to separate the data out in the PO_ITEM_NBR field.

Current
PO_ITEM_NBR Result PO_ITEM PO_NBR
123344 001 123344 001
123344 002 123344 002
123455 001 123455 001
etc.

This can be a permament change because the data will not be manuplated. I am
not sure where the best place to do this. The data will be displayed in a
form and used in a report.

Assuming the space is there in every record, the two values
can be calculated like this:

PO_ITEM: Left(PO_ITEM_NBR, Instr(PO_ITEM_NBR, " ") - 1)

PO_NBR: Mid(PO_ITEM_NBR, Instr(PO_ITEM_NBR, " ") + 1)

 

[John Spencer]

Use Instr to locate the space and then split the item there.

Field: PO_Item: LEFT(Po_Item_Nbr, Instr(1, Po_Item_Nbr & " "," ")-1)

Field: Po_Nbr: Mid(Po_Item_Nbr, Instr(1, Po_Item_Nbr & " "," ")+1)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..