Left trimming text

  • Thread starter Thread starter Don Anderson
  • Start date Start date
D

Don Anderson

I have a simple query from 2 tables that provides data for a simple report.
One of the columns is [NickName], but since there are more than one Don or
one Char the field will be filled as follows:

[NickName]
Bill
John
Don (K)
Char (B)
Don (A)
Char (H)
Kris
Etc...

In one report that uses the query I need to use the nickname just as it is,
but in another report that uses the same query I just need to use the first
name of the Nickname (ie, Bill, John, Don, Char, Don, ...).

How can I do the Access equivalent of an Excel Formula of:
Left(NickName, Find(" ", NickName))

Thanks in advance,
Don
 
Don said:
I have a simple query from 2 tables that provides data for a simple report.
One of the columns is [NickName], but since there are more than one Don or
one Char the field will be filled as follows:

[NickName]
Bill
John
Don (K)
Char (B)
Don (A)
Char (H)
Kris
Etc...

In one report that uses the query I need to use the nickname just as it is,
but in another report that uses the same query I just need to use the first
name of the Nickname (ie, Bill, John, Don, Char, Don, ...).

How can I do the Access equivalent of an Excel Formula of:
Left(NickName, Find(" ", NickName))
Click to expand...


That expression is probably inadequate when there is no
second part or if the NickName field is null. Here's one
that deal with all those situations:

Left(NickName, NZ(IIf(InStr(NickName, " ") > 0,
InStr(NickName, " ") - 1, Len(NickName)), 0)) If you have
towrite that gobbledygook more than once, you'd be better
off writing a function to encapsulate the complexities.
 
Thank you very much, that worked perfectly.


Marshall Barton said:
Don said:
I have a simple query from 2 tables that provides data for a simple report.
One of the columns is [NickName], but since there are more than one Don or
one Char the field will be filled as follows:

[NickName]
Bill
John
Don (K)
Char (B)
Don (A)
Char (H)
Kris
Etc...

In one report that uses the query I need to use the nickname just as it is,
but in another report that uses the same query I just need to use the first
name of the Nickname (ie, Bill, John, Don, Char, Don, ...).

How can I do the Access equivalent of an Excel Formula of:
Left(NickName, Find(" ", NickName))
Click to expand...


That expression is probably inadequate when there is no
second part or if the NickName field is null. Here's one
that deal with all those situations:

Left(NickName, NZ(IIf(InStr(NickName, " ") > 0,
InStr(NickName, " ") - 1, Len(NickName)), 0)) If you have
towrite that gobbledygook more than once, you'd be better
off writing a function to encapsulate the complexities.
Click to expand...
 
Back
Top