• Status: Solved
• Priority: Medium
• Security: Public
• Views: 232

# Access - Truncating a Field

In the following example:

366540 - Doe, John

I would like to separate this on field into three fields:

1. ID (366540)
2. Last Name (Doe)
3. First Name (John)

The Dash always separates the ID from the last and first names, and the comma always separates last name from first name

Thanks
Thanks
0
tahirih
• 4
• 4
1 Solution

Commented:
The code below will do it for you
Cheers, Andrew
``````SELECT Table4.Field1
, Trim(Left([Field1],InStr([Field1],"-")-1)) AS F1
, Trim(Mid([Field1],InStr([Field1],"-")+1,Len([Field1])-Len([F1])-Len([F3])-2)) AS F2
, Trim(Right([Field1],Len([Field1])-InStrRev([Field1],","))) AS F3
FROM Table4;
``````
0

Author Commented:
This worked wonderfully. I do want to SELECT all fields, and insert into a new Table C:

I was not able to get the following to work:

SELECT * FROM A INTO C
, Trim(Left([Provider],InStr([Provider],"-")-1)) AS F1
, Trim(Mid([Provider],InStr([Provider],"-")+1,Len([Provider])-Len([F1])-Len([F3])-2)) AS F2
, Trim(Right([Provider],Len([Provider])-InStrRev([Provider],","))) AS F3;

Thanks
0

Author Commented:
Also, the comma remains in F2, the Last Name field. Can we please recode to remove this?

Thank you.
0

Commented:
Slight increase in the number to take off the extra characters in F2, had to changeteh -2 to -4 to compensate for the spaces that dont go into F1 and F3
Cheers, Andrew

``````SELECT Table4.Field1
, Trim(Left([Field1],InStr([Field1]," - ")-1)) AS F1
, Trim(Mid([Field1],InStr([Field1],"-")+1,Len([Field1])-Len([F1])-Len([F3])-4)) AS F2
, Trim(Right([Field1],Len([Field1])-InStrRev([Field1],", "))) AS F3
FROM Table4
``````
0

Author Commented:
I would also like to create a new table C based on this SQL query. This offers a Query view, not a new table.

Thank you
0

Commented:
OK
``````SELECT Table4.Field1
, Trim(Left([Field1],InStr([Field1]," - ")-1)) AS F1
, Trim(Mid([Field1],InStr([Field1],"-")+1,Len([Field1])-Len([F1])-Len([F3])-4)) AS F2
, Trim(Right([Field1],Len([Field1])-InStrRev([Field1],", "))) AS F3
INTO [Table C]
FROM Table4
``````
0

Author Commented:
I was placing the INTO in the improper location. Thanks
0

Commented:
No problem and best wishes, Andrew
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.