• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

Update Query Using Left()

Hello,

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?
0
Ernest Grogg
Asked:
Ernest Grogg
1 Solution
 
PatHartmanCommented:
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);
0
 
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);


Thanks!
0
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now