Mid, Left, Right Functions

Hi,

The records in my table field contains [first name], [last name] together and I can't remember how to separate them without defining the number of characters. What I would is to read any alpha characters before the comma (for first name) and alpha characters after the comma (for last name).
Please help. :(

I think you may want to use the Instr function to return the location of the string that you are trying to use for example:


Index = Instr(1, "Bush, George", ",") would return 5.


Thereafter, you can use Mid, Left or Right Functions. As for me, I would use Mid function thus:

LastName = Mid (vFullName, 1, (Index - 1)) would return
"Bush"

In this case I'm assuming that the variable vFullName = "Bush, George"

Cheers!

The Alchemist.

Quote:

---

Originally Posted by Shell

Hi,
The records in my table field contains [first name], [last name] together and I can't remember how to separate them without defining the number of characters. What I would is to read any alpha characters before the comma (for first name) and alpha characters after the comma (for last name).
Please help. :(

---

Here is two functions that will help you

staff_first will give you the person's first name, staff_surname will give the person's surname. This assume the person's name is in the format [first name], [last name]

staff_name is string [first name], [last name] e.g. "James,Brown"

Function staff_surname (staff_name)

Dim temp_name As String
Dim position_indicator As Integer

temp_name = Trim(staff_name)
position_indicator = InStr(temp_name, ",")
If position_indicator > 0 Then
staff_surname= Trim(Mid(temp_name, position_indicator + 1))
Else
staff_surname = Null
End If

End Function

Function staff_first(staff_name)

Dim temp_name As String
Dim position_indicator As Integer

temp_name = Trim(staff_name)
position_indicator = InStr(temp_name, ",")

If position_indicator > 0 Then
staff_first = Trim(Mid(temp_name, 1, position_indicator - 1))
Else
staff_first = Null
End If

End Function