Solved

Get text before space in access

Posted on 2009-04-02
4
325 Views
Last Modified: 2012-05-06
I have a field formatted like the following:

last.first (last, first mi)

I need to get just the last.first portion (everything before the first space).  

What's best way to do this?

Thanks,
Matt
0
Comment
Question by:mattturley
  • 2
  • 2
4 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 24051837
Left(Last.First,Len(Last.First," ")-1)

Flyster
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 24051863


Left(LastFirst,Instr(1, LastFirst," ")-1)

mx
0
 
LVL 75
ID: 24051891
Exmaple

Left("joe, jammer",Instr(1, "Joe, Jammer"," ")-1)

returns

joe,

including the comma.

mx
0
 
LVL 22

Expert Comment

by:Flyster
ID: 24051973
DatabaseMX is correct. I meant to use Instr instead of Len.
Left([Last.First],InStr([Last.First]," ")-1)

If you don't want the comma to show, change the -1 to -2.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

820 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