using the LEN function

I am trying to searh for a full name in a database.
I have a single text box where the user will enter in their full name (First & Last). I am having trouble writing a function to separate the full name in to two vaiables. One being the First Name and the other being the Last Name.
I need to do this before I can search the database

Can anyone help?

You could use mid on the position of the space betwen the names, but I would suggest using two boes, one for each name.

Reason being the world is full of peanuts who will enter their name surname, firstname instead of what you are expecting ie firstname, surname.

In my experience you can always count on a user to do the unexpected. :)

Try this:

Code:

---

Option Explicit

Private Sub Command1_Click()
    Dim FirstName As String
    Dim LastName As String
    Dim SpacePos As Integer
   
    ' Find the position of the first space in the text box
    SpacePos = InStr(1, Trim$(Text1), " ")
   
    ' Everything to the left of the space is the first name
    FirstName = Trim$(Left$(Text1, SpacePos - 1))
   
    ' Everything to the right of the space is the last name
    LastName = Trim$(Right$(Text1, Len(Text1) - SpacePos))
   
    ' Display the names individually
    MsgBox "First Name = " & FirstName & vbNewLine & _
          "Last Name = " & LastName
End Sub

---

In case you dont know, Trim() removes leading and trailing spaces from a string. This allows the user to accidentally put a space before the first name and the function will still work.

[Edited by seaweed on 11-09-2000 at 07:51 PM]

Lets say Full Name is in Text1

Private Sub Command1_Click()
Dim x() As String
x = Split(Text1, " ")
Text2 = x(0)
Text3 = x(1)
End Sub

Text2 will have first name, text3 will have last name.