Convert Text to Number in Multivalue Table Field without data loss

[accote]

Hi



Big problem here, I have a multivalue text field called "Groups" that is generated through a relationship
with another table called "Comments". When I set this up I left the field as a text field when it should
have been a number field, and now I can't use the database with sharepoint, because the field needs
to be a number field.
My Question is there a way to convert it without loosing all my data, as there are over 5000 records?
My Table with the multivalue field is called "Contacts", the field is called "Groups".

I tried update and append queries but I must be doing something wrong, so detailed explanation would
be very much appreciated.

Thank You

[June7]

I just tested changing a multi-value text field to number type and no data was lost. Make copy of your table and test. The related field in Comments will also have to be number type.

[accote]

Got it resolved, thought I share if someone else has the same dilemma.
Created another MVF called Groups1 and set it as a Number field then ran the following VB Function.

Public Function UpdatMVFField()
On Error GoTo err_proc
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String

strSQL = "SELECT ID, CommentID " & _
"FROM Contacts INNER JOIN Comments ON Contacts.Groups.Value = Comments.Comment " & _
"WHERE (((Comments.CommentID) Is Not Null)) " & _
"ORDER BY Contacts.ID;"

Set dbs = CurrentDb
With dbs
Set rst = .OpenRecordset(strSQL, , dbReadOnly)
While rst.EOF = False
strSQL2 = "INSERT INTO Contacts ( Groups1.[Value] ) " & _
"VALUES ( " & rst!CommentID & " ) " & _
"WHERE (((Contacts.ID)=" & rst!ID & "));"
.Execute strSQL2, dbFailOnError
rst.MoveNext
Wend
End With


exit_proc:
On Error Resume Next
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Exit Function

err_proc:
MsgBox Err.Description
Resume exit_proc

End Function