Spliting One Field into Two

[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: Sub RoomConverstion()

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


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

If there is anything else wrong please let me know.

 

[PHV]

The Room number always beigns after the first 4 characters
You may try this:
DoCmd.RunSQL "UPDATE Staff SET [Room #]=Mid(RMID,5) WHERE Len(RMID)>4"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mwn1218]

Thank you so much. It worked great. One last question... do you have any idea why that error would come up?

 

[PHV]

I guess you tried to open a Recordset without Connection.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244