split field into elements using update query (1 Viewer)

[y2k]

Is it possible to split a field (eg name) into different elements (eg first name, last name etc)? I'm using an update query to update a table from an externamlly linked database (linked table) and need to split a field into individual elements.

Can anybody help?

 

[Geoff Codd]

show me an example of how the data looks, is there a gap between first last name or a comma etc

 

[y2k]

They're seperated by a coma in the format last name,first name. However, I also want to do this to a date which is not seperated by anything so how would I go about that. Would it be the exact same? The date is in MMDDYY and I need to split it into MM, DD & YY in 3 different fields. There are no characters between te month and day and between the day and year. Any ideas at all?

Thanx!!!

 

[David R]

InStr() will find the position of the comma in your name field. Then use Right() and Left() to separate out the actual words from the comma.

Fixed length fields are even easier. Use Left(), Mid(), Right() to break out the pieces in the lengths you need.

 

[Geoff Codd]

for first name use FirstName: Left([name],InStr(1,[name],",")-1)
for last name use LastName: Right(Trim([name]),Len(Trim([name]))-InStr(1,[name],","))

Are you really sure that you want to split the date into 3 fields would it not me better to convert it to mm/dd/yy

 

[y2k]

Left(), Mid() & Right() sounds like exactly what I need. can you help me out on it.. what's the syntax????

Yes, I need to split these fields as they're used later on.

Thanx!!

 

[Geoff Codd]

month = month: Left([date],2)
day = day: Mid([date],3,2)
year = year: Right([date],2)

 

[David R]

Or you can convert it to a real date and just use the Month(), Day(), Year() functions when you need them.
http://www.access-programmers.co.uk/ubb/Forum3/HTML/003312.html talks about a few ways to convert text dates in strange formats to a real field.