how can I Split field data in a table

[Guest]

Hi, I have a table with a field named "Name". It contains person's name and
Job Title separated by "-"(dash). How can I split this field so that I can
have name and job title in two separate fields. Do I need to run a query?
Thanks


AJ

 

[Guest]

Field1 = Left([Name],Instr(1,[Name],"-",1)-1)
Field2 = Mid([Name],Instr(1,[Name],"-",1)+1)

 

[Graham R Seach]

AJ,

You shouldn't have two items in the same field anyway. This breaks one of
teh cardinal rules of database design - one type of data per field!

Among many, many others, I counsel you to modify your table design to add an
extra field for JobTitle. This single act will eliminate the miriad of
problems you will cause for yourself (and others) by having them in the same
field. If you've done the same thing anywhere else - change them too, and do
it now, before it becomes a real problem!

But to get you out of the first of the problems you'll experience, use a
query. The following assumes you have added a JobTitle field:

UPDATE tblMyTable
SET JobTitle = Mid([Name], Instr(1, [Name], "-") + 2),
[Name] = Left([Name], Instr(1, [Name], " -") -1)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[fredg]

Hi, I have a table with a field named "Name". It contains person's name and
Job Title separated by "-"(dash). How can I split this field so that I can
have name and job title in two separate fields. Do I need to run a query?
Thanks

AJ

Do you really have a field named "Name"?
Name is a reserved Access/VBA/Jet word and should not be used as a
field name.
See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'