Solved

Get text before space in access

Posted on 2009-04-02
4
321 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 - Access MVP) 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

706 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

19 Experts available now in Live!

Get 1:1 Help Now