Trim after space

[Jay]

In a query I want to remove anything after a space in a
field. Example is First_Name has a middle inital in it.
In my query I want to remove this. Does every know a name
of a function that will do this?

Now:
First_Name
Jon M

Looking for:
First_Name
Jon

Thanks for the help.

 

[Bruce M. Thompson]

In a query I want to remove anything after a space in a

field. Example is First_Name has a middle inital in it.
In my query I want to remove this. Does every know a name
of a function that will do this?

Now:
First_Name
Jon M

Looking for:
First_Name
Jon

Update to:

Mid([First_Name],1,instr([First_Name]," ")-1)

 

[Jay]

I received a #Error on some of the records.

-----Original Message-----

In a query I want to remove anything after a space in a
field. Example is First_Name has a middle inital in it.
In my query I want to remove this. Does every know a name
of a function that will do this?

Now:
First_Name
Jon M

Looking for:
First_Name
Jon

Update to:

Mid([First_Name],1,instr([First_Name]," ")-1)

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)within the newsgroups so that all might benefit.<<


.

 

[Shane G. Brodie]

I assume you have one or more records where there is no middle name and
hence no space ... try a small variation on Bruce's example:

IIf(InStr([Table1]![F_Name]," "),Mid([F_Name],1,InStr([F_Name],"
")-1),[Table1]![F_Name])


Regards


--
Shane Brodie
IT Consultant
c/o MANITOBA FILM & SOUND
410-93 Lombard Avenue
Winnipeg MB R3B 1B3
tel 204-947-2040
website: http://www.mbfilmsound.mb.ca
e-mail: (e-mail address removed)

I received a #Error on some of the records.

-----Original Message-----

In a query I want to remove anything after a space in a
field. Example is First_Name has a middle inital in it.
In my query I want to remove this. Does every know a name
of a function that will do this?

Now:
First_Name
Jon M

Looking for:
First_Name
Jon

Update to:

Mid([First_Name],1,instr([First_Name]," ")-1)

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)

No Email, Please. Keep all communications

within the newsgroups so that all might benefit.<<


.

 

[Bruce M. Thompson]

I received a #Error on some of the records.

Oops! My bad. Try this instead (watch for line wrap - it's all on one line):

IIf(Not IsNull([First_Name]), IIf(instr([First_Name],"
"),Mid([First_Name],1,instr([First_Name]," ")-1), [First_Name]),
[First_Name])

Sorry about that.

 

[Jay]

Worked Thanks!!

-----Original Message-----
I assume you have one or more records where there is no middle name and
hence no space ... try a small variation on Bruce's example:

IIf(InStr([Table1]![F_Name]," "),Mid([F_Name],1,InStr ([F_Name],"
")-1),[Table1]![F_Name])


Regards


--
Shane Brodie
IT Consultant
c/o MANITOBA FILM & SOUND
410-93 Lombard Avenue
Winnipeg MB R3B 1B3
tel 204-947-2040
website: http://www.mbfilmsound.mb.ca
e-mail: (e-mail address removed)

I received a #Error on some of the records.

-----Original Message-----
In a query I want to remove anything after a space in a
field. Example is First_Name has a middle inital in it.
In my query I want to remove this. Does every know

a
name

of a function that will do this?

Now:
First_Name
Jon M

Looking for:
First_Name
Jon

Update to:

Mid([First_Name],1,instr([First_Name]," ")-1)

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)
No Email, Please. Keep all communications
within the newsgroups so that all might benefit.<<


.

.