Quick sort using Access - parsing data - need some help

Posted on 2012-09-21
Medium Priority
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

IrogSinta earned 1200 total points
ID: 38423938
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

plummet earned 400 total points
ID: 38424306

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 40

Assisted Solution

als315 earned 400 total points
ID: 38424782
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

ID: 38425298
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 to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

840 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