Quick sort using Access - parsing data - need some help

Posted on 2012-09-21
Last Modified: 2012-09-22
Running Access 2000 on a pc running Windows XP pro

I just want to do a quick sort of data.  I am importing one data column from Excel. An example of the data in the field is:

John Peters, programmer

I imported the data into an Access table called tblImport.  It has one field called [Field1].

I want to separate first name from last name.  I did this in a query using the following query:

Expr_FirstNamePos: InStr(1,[Field1]," ")

Exp_FirstName: Trim(Left([Field1],[Expr_FirstNamePos]))

Expr_CommaPos: InStr(1,[Field1],",")

Expr_Diff: ([Expr_CommaPos]-[Expr_FirstNamePos]-1)

Expr_LastName: Mid([Field1],([Expr_FirstNamePos]+1),([Expr_Diff]))

Yes, it’s crude but it works.  

My question:  
I want to sort  Exp_FirstName and Expr_LastName in ascending order.  When I try to do this Access presents a dialog box asking me to enter a parameter value.  So obviously Access is confused.  

1.) Do I really have to write VB code to do this?   I just want to do a quick sort, not a fancy solution

2.) Is there some way to do this in Excel without using Access? I can’t seem to use Instr in an Excel cell without writing VB Code.

Thank you for your help.
Question by:donpick
    LVL 29

    Accepted Solution

    You don't have to write vba code to do it in Access but let me just give the answer in Excel since that seems preferable with you.

    If the column of names is in cell A1:
    Cell B1:   =LEFT(A1,FIND(" ",A1)-1)
    Cell C1:   =MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(",",A1)-FIND(" ",A1))
    LVL 10

    Assisted Solution


    I think it should work. I did a little test here and it is fine, though my query is a little different. If you can post your SQL here I'll have a look and see if I can replicate the problem; my SQL code follows:

    SELECT Left$([Import_col],InStr([Import_col],",")-1) AS Exp_FirstName, Mid$([Import_col],InStr([Import_col],",")+1) AS Expr_LastName
    FROM Table1
    ORDER BY Left$([Import_col],InStr([Import_col],",")-1), Mid$([Import_col],InStr([Import_col],",")+1);

    Open in new window

    I created a test table Table1 with one field called Import_col and typed in a few examples similar to yours. It works fine, but I am doing the left() and mid() functions in one hit rather than creating temporary columns, which is probably the cause of your problem
    LVL 39

    Assisted Solution

    You can also add this function to your excel file:
    Public Function FName(Str As String) As String
    FName = Split(Str, ",")(0)
    FName = Split(FName, " ")(1)
    End Function

    Open in new window

    Look at sample. You can use this function in Access also

    Author Closing Comment

    Hello  IrogSinta :  I know very little about Excel.  Thank you for showing me the Find function.  Apparently it acts like Instr.

    Hello als315  :  I didn't know Split could be applied directly to a function within Excel.  This is useful to know.

    I am a paying subscriber so I appreciate your prompt answers.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now