Run a Query to separate names from one field to two

[RAxel]

Basically I have one field in excel that I've imported into Access, called 'Name'. In it, looks like this:

Name
dover, ben
last, first
name, fake
etc....

Basically, it's last name, first name in the column. I want to separate them into two fields, one with first name and the other with last name with no comma. Is this possible to do in Access (without the help from a programming language, like .NET)

I want:
Last Name First Name
Dover Ben
Last First
Name Fake

Thanks.

 

[Golom]

Short answer ... yes ... it is possible. Access however has at least two "languages" (SQL and VB) that it uses. Which do you want to use?

In SQL

Select Left([Name], Instr([Name],",")-1) As LastName,
       Mid ([Name], Instr([Name]," ")+1) As FirstName
From .... etc. ...

In VB

Dim FirstName As String
Dim LastName  As String
Dim rs As DAO.Recordset
Set rs = Currentdb.OpenRecordset ("Select [Name] From tbl")
Do Until rs.EOF
   FirstName = Mid (rs![Name], Instr(rs![Name]," ")+1)
   LastName  = Left(rs![Name], Instr(rs![Name],",")-1)
   Debug.Print FirstName & " " & LastName
   rs.MoveNext
Loop

 

[RAxel]

Thanks a bunch Golom. Do you know of any good books to read through (or purchase) to help out with SQL problems, like this?