Take one part of a field to a different field

[IFORD]

I have a field1 that has this infor in it
E201 1 1 3 STEPHEN H LIGHT

I need a field that just has the

stephen h light

Can someone tell me how to make a query that will just give me that information I have 5179 record and i really dont want to do it by hand.

Thank you for your help Irene

 

[lespaul]

is the information prior to the name in a static format? So every person's name starts at the same position in the string or can the prior information be variable?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[IFORD]

EVERY NAME START AT THE SAME PLACE

 

[lespaul]

something along these lines, you may need to adjust the 15 to the actual starting point in the field:

SELECT MID([YourFieldName], 15, length([YourFieldName])) FROM TableName

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[IFORD]

Leslie, This is what a put in a select query and i get a error

SELECT MID([FIELD1], 15, length([FIELD1])) FROM STANMARTINFORDNAMESREMOVED

I get a undefied function 'length' in expressin error

 

[lespaul]

it's probably the wrong function name, try LEN (and if that's not it, search the Access help for Length Function and get the correct name)

 

[IFORD]

Thank worked thank you I just change the 15 to 42

 

[PHV]

And no need of the Len function:
SELECT Mid([FIELD1], 42) AS FullName
FROM STANMARTINFORDNAMESREMOVED

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

Thanks PHV, I wasn't sure if the last parameter was optional or not, I guess it is!

Leslie