Split up information in a single field

[sxmxfan]

Hi,

I am trying to split up data from one field of a record into 2 fields of a record. Here some sample data;

Orginal Field
Plain
Xtreme-Slash
Mark/Ventura2 XXS
Yellow
Red XXL

This is expression I used for the 1st new field.
ColorOP: Left([family_members]![friendly_name],InStr(1,[family_members]![friendly_name]," ")-1)

Results 1st New Field
#Error
#Error
Mark/Ventura2
#Error
Red

This is expression I used for the 2nd new field.
SizeOP: Mid([family_members]![friendly_name],InStr(1,[family_members]![friendly_name]," ")+1)

Results 2nd New Field
Plain
Xtreme-Slash
XXS
Yellow
XXL

The result I would like to see is:

1st Field 2nd Field
Plain
Xtreme-Slash
Mark/Ventura2 XXS
Yellow
Red XXL

I believe that error I get from "Plain, Xtreme-Slash, and Yellow" is because the is no space after them.

[MMcCarthy]

Try ...

 
ColorOP: Left([family_members]![friendly_name],nz(InStr(1,[family_members]![friendly_name]," "), Len([family_members]![friendly_name]) + 1) -1)

[NeoPa]

Try ...

ColorOP: Left([family_members]![friendly_name],nz(InStr(1,[family_members]![friendly_name]," "), Len([family_members]![friendly_name]) + 1) -1)

Another, similar trick would be :

ColorOP: Left([family_members].[friendly_name],InStr(1,[family_members].[friendly_name] & " "," ")-1)