By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,326 Members | 1,919 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,326 IT Pros & Developers. It's quick & easy.

Split up information in a single field

P: 1
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.
Dec 1 '06 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. ColorOP: Left([family_members]![friendly_name],nz(InStr(1,[family_members]![friendly_name]," "), Len([family_members]![friendly_name]) + 1) -1)
  3.  
Dec 1 '06 #2

NeoPa
Expert Mod 15k+
P: 31,661
Try ...

Expand|Select|Wrap|Line Numbers
  1. ColorOP: Left([family_members]![friendly_name],nz(InStr(1,[family_members]![friendly_name]," "), Len([family_members]![friendly_name]) + 1) -1)
  2.  
Another, similar trick would be :
Expand|Select|Wrap|Line Numbers
  1. ColorOP: Left([family_members].[friendly_name],InStr(1,[family_members].[friendly_name] & " "," ")-1)
Dec 1 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.