split one field into two

[Mike Hughes]

Trying to split
CASE/MEMBERID field 00041130P10005573

into

CASE 00041130P
MEMBER 10005573

Would someone please show me how to do this? Thanks

 

[wh00t]

If the data length is static you can simply do the following

Case: Left(Field,9)
Member: Right(Field,8)

 

[Mike Hughes]

I tried something like this and it doesn't work --Line_01 is the table name and I created two new fields in the table, Case and Member

Update Line_01
Case: Left([Case/Member ID],9)
Member: Right([Case/Member ID],8)

 

[wh00t]

Are you trying to store the values in seperate fields or just splitting the 1 field for displaying?

If it is for displaying in a query you would enter
SELECT Left([Case/Member ID],9) AS [Case], Right([Case/Member ID],8) AS [Member]
FROM Line_01;

If you are storing the values in a field in a table you would use something like this
UPDATE Line_01 SET Line_01.Case = Left([Case/Member ID],9), Line_01.Member = Right([Case/Member ID],8);

Although I do not see the need to store them in seperate fields as it would be duplicating data.

 

[Mike Hughes]

This is the one I used and it worked great! thanks for your help.

UPDATE Line_01 SET Line_01.Case = Left([Case/Member ID],9), Line_01.Member = Right([Case/Member ID],8);