Trim after space

  • Thread starter Thread starter Jay
  • Start date Start date
J

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.
 
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
Click to expand...

Update to:

Mid([First_Name],1,instr([First_Name]," ")-1)
 
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
Click to expand...

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.<<


.
Click to expand...
 
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)

Jay said:
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
Click to expand...

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
Click to expand...
within the newsgroups so that all might benefit.<<


.
Click to expand...
Click to expand...
 
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. :-)
 
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)

Jay said:
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
Click to expand...
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.<<


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top