update query to split field

I have combined values in a field that I would like to split. Field1 has values like

I need to split off everything from the hypen over and add it to field2. I'm not sure how to set the criteria to include only those records with the "-" and how to isolate and move the values to the right of the hypen.

What is the best way to go about this?
Who is Participating?
brad2575Connect With a Mentor Commented:
Did this work for you?  I wrote this in MSSQL query but this "should" work in Access as well but may not because some of the functions are more advanced.
try this:
select Substring(FieldToFind, 0, CharIndex('-', FieldToFind)) as LeftOfDash ,
Substring(FieldToFind, CharIndex('-', FieldToFind), LEN(FieldToFind))  as RightOfDash,
Substring(FieldToFind, CharIndex('-', FieldToFind) + 1, LEN(FieldToFind))  as RightOfDashNotINcludingFirstDash
From TableName
where CharIndex('-', FieldToFind)  > 0

Open in new window

This gets you the fields but does not do the update.  Do you need the query to do that?  Or can you get that from the above?
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

eliwilAuthor Commented:
I'm getting a syntax error. Could you give me the complete statement? the table is TestResults and the field is Lot.
What Version and type of SQL are you using?  MYSQL, MSSQL?
select Substring(LOT, 0, CharIndex('-', LOT)) as LeftOfDash ,
Substring(LOT, CharIndex('-', LOT), LEN(LOT))  as RightOfDash,
Substring(LOT, CharIndex('-', LOT) + 1, LEN(LOT))  as RightOfDashNotINcludingFirstDash
From TestResults 
where CharIndex('-', LOT)  > 0

Open in new window

eliwilAuthor Commented:
Sorry, I thought the question was posted to Access SQL rather than just SQL syntax. Should have double checked. Appreciate your assistance.
eliwilAuthor Commented:
It didn't work, but that's my fault for posting it here and not in Access. Thanks for your assistance. I've reposted the question in Access.
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.

All Courses

From novice to tech pro — start learning today.