Update Query Using Left()


I want to split a field in two but I got the first part working but can't seem to get the second half of the sql working

UPDATE MyTableName SET MyTableName.FirstPart = Left([Combo] & " ",InStr([Combo] & " "," ")-1);

The second part should contain the Right()  but since the amount of data fluctuates I can't seem to get it right.

I tried using 2, and 28 respectively but that isn't consistant. What is is the first part I got here except too that if there are no "Characters" such as AB then leave it alone for the Left() (should be an IIF statement?) but lost on how to configure that one, then the rest should be on the Right()

any Ideas?
Ernest GroggAsked:
Who is Participating?
PatHartmanConnect With a Mentor Commented:
Because the left is variable, the right is also so you have to use Mid() instead

UPDATE MyTableName SET MyTableName.FirstPart = Left([Combo] & " ",InStr([Combo] & " "," ")-1)
MyTableName.SecondPart = Mid(Combo, Instr(Combo, ",") + 1);

If there is "always" a space after the comma, then use +2 to get the starting point for the second half of the string.

If the reason you are concatenating the space after the combo is to handle, nulls, you can use the NZ() function instead.
MyTableName.SecondPart = Mid(Combo, Nz(Instr(Combo, ","), 0) + 1);
Ernest GroggAuthor Commented:
Perfect.  Here is what worked for me:

UPDATE MyTableName SET MyTableName.FirstPart = Left([Combo] & " ",InStr([Combo] & "2","2")-1), MyTableName.SecondPart= Mid([Combo],NZ(InStr([Combo],""),0)+2);

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.