Split One Field into two fields

[mwn1218]

I am trying to clean up some data that a client gave us. Right now the Building and Room # are in the same field and I want to split them apart. The Room number always beigns after the first 4 characters. I was trying to write a module to do this automatically. When I run the module I get a Compile error: Variable required - can't assign to this expresstion
and it highlights what is in blue below.

Sub RoomConverstion()

Dim rsStaff As New ADODB.Recordset
Dim intLen As Long
Dim strRoom As String
Dim strOldRoom As String

rsStaff.Open "Staff"

strRoom = ""
intLen = 4

Do Until rsStaff.EOF

strOldRoom = rsStaff![RMID]

strRoom = Right(strOldRoom, Len(strOldRoom - intLen))

rsStaff![Room #] = strRoom
Loop
End Sub

This is the first time I have tried something like this so if there is any suqqestions to the code I am open to them.

Thanks for the help.

 

[ByteMyzer]

How about:

Sub RoomConverstion()
    CurrentDb.Execute "UPDATE [Staff] " _
        & "SET [Room #]=Mid([RMID],5);"
End Sub

See if this works for you.

 

[mwn1218]

Thanks for the reply. I ended up using this:

DoCmd.RunSQL "UPDATE Staff SET [Room #]=Mid(RMID,5) WHERE Len(RMID)>4"

 

[ByteMyzer]

Not bad, but the disadvantage with using DoCmd.RunSQL is that you have to disable confirmations before executing the line. If instead you use:

[b]CurrentDb.Execute[/b] "UPDATE Staff SET [Room #]=Mid(RMID,5) WHERE Len(RMID)>4"

...then you do not.

 

[sam77755]

left()

an Easier way is to create 2 new fields in your table (Building, RoomNumber) and use the left() fuction to split the original field and assign it's value to a variable then save it in the newly created field.