help needed to split up a text field

[SamDeMan]

Hi
I am having problems with this code. can anybody spot the problem. i am trying to get employee names into a list on my form. thats it!!!! don't know why it doesn't work.

btw, what i am really trying to do is get a field "employee" which includes first and last name to be split up. can anybody tell me the easiest way to use the split function. i want that the user should be able to search and sort last names as well as first names.

thanks,
sam

following is my code.

Private Sub Form_Open(Cancel As Integer)
Dim rs As ADODB.Recordset
Dim strMySQL As String
On Error Resume Next

Set rs = New Recordset
strMySQL = "SELECT AgencyLogTbl.EmployeeName As Emp FROM AgencyLogTbl" & _
" WHERE AgencyLogTbl.EmployeeName Is Not Null"
rs.Open strMySQL, CurrentProject.Connection
rs.MoveFirst
Do While rs!Emp
If Not IsNull(rs!EmployeeName) Then
ListName.AddItem rs!Emp
End If
rs.MoveNext
Loop
Set rs = Nothing
End Sub

 

[ChrisO]

I think you are seeing the result of not having atomic data fields.

What I would think is the minimum number of fields required…

PreTitle
FirstName
MiddleName
LastName
PostTitle
Qualifications
PreferredName

Even FirstName may not be atomic if the employee has some unexpected name.

'Mr.' – 'Who Flung' – 'A.' - 'Duck' – 'Esq.' - 'NBG, ASAP' – 'Fred'

Qualifications could even be on the many side simply because, when qualifications are important, all qualifications are important and will vary in number.

PreferredName can be required when writing reports such as comments in a school report. First name might be Christopher, and is required for legal reasons for the report header, but to continually write Christopher when the parents prefer Chris does not look so good.

So I think the aim should be to permanently split the EmployeeName field into however many atomic fields as are required. It will not only reduce parsing code now but also help to reduce it in the future.

Apart from that, parsing an 'EmployeeName' field could prove impossible.

Regards,
Chris.