Split Function Syntax Problem

[Keith]

I have a table(tblMaster) with a field(SUBS1) that contains a string of names seperated by a space. I want to split the field into names and put each name together with the ID field into a separate table(tblBoatMember). I get an 'Invalid use of Null' error on the line vArr = Split(rsOrig("SUBS1"), " ") I have used the code a long time ago and it worked albeit with different tables. The Code compiles ok. Its probably something obvious but the grey matter is getting a bit old now.

The code i'm using is:

Public Function BreakToWords()
Dim rsOrig As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim vArr As Variant 'array to hold the split phrase
Dim i As Integer 'counter

Set rsOrig = CurrentDb.OpenRecordset("tblMaster")
Set rsNew = CurrentDb.OpenRecordset("tblBoatMember")

If rsOrig.RecordCount <> 0 Then

'loop the rsOrig records
rsOrig.MoveFirst
While Not rsOrig.EOF
'split the phrase on a space delimiter

[COLOR="Red"]vArr = Split(rsOrig("SUBS1"), " ")[/COLOR]
'loop the array (words) and add to rsNew
For i = 0 To UBound(vArr)
With rsNew
.AddNew
.Fields("ID") = rsOrig("ID")
.Fields("Boat") = vArr(i)
.Update
End With
Next

rsOrig.MoveNext
Wend

End If

rsOrig.Close
rsNew.Close
Set rsOrig = Nothing
Set rsNew = Nothing

End Function

 

[vbaInet]

I'm sure you know this Keith What would you do or what function would you use to catch Nulls?

rsOrig("SUBS1") is returning Null.

 

[Keith]

Something has gone wrong, I didn't expect anthing to be written to the table as I had an error but The table now has 9,000 entry's with each ID entered 4 times with the four same boats. I take your point about the Nz function.

 

[vbaInet]

Perhaps you want to validate that field to ensure that a value is entered during Save. Then perform some housekeeping to dig out and fix all the Nulls.

 

[Keith]

Thanks, I ran a query to remove all the records with no data in the field and it worked a treat. I'm trying to 'Normalize' tables in a DB i've taken over that was in a right mess.

 

[vbaInet]

I hope those deleted records won't be needed in the future. I guess you've got a backup somewhere.

Good luck with the normalization process!

 

[Keith]

The data was safe, I ran a make table query and ran the function off that

 

[vbaInet]

Clever lad