TRIM function with fixed length field

[JoeA2006]

I am importing records with a fixed length description field. I need to
create a field from the last word of the description(everything to the right
of the last space). The field length is 30. When I use the function below --
it appears the trim function does not seem to shorten the string for the
InStrRev function.

SortString: Mid(Trim([DESCRIPTION]),(InStrRev[DESCRIPTION]," "))+1)
I have also used Rtrim and it doesnt work either

 

[JoeA2006]

I meant to mention I am using this in an append query. I have a column to
append it to, but I am wondering if that is the problem

 

[Klatuu]

The Trim function only removes leading a training spaces from a string, so it
is not appropriate for this situatuion. Additionally, there were some extra
parentheses you don't need.

SortString: Mid([DESCRIPTION], InStrRev([DESCRIPTION]," ")+1)
--
Dave Hargis, Microsoft Access MVP

I meant to mention I am using this in an append query. I have a column to
append it to, but I am wondering if that is the problem

I am importing records with a fixed length description field. I need to
create a field from the last word of the description(everything to the right
of the last space). The field length is 30. When I use the function below --
it appears the trim function does not seem to shorten the string for the
InStrRev function.

SortString: Mid(Trim([DESCRIPTION]),(InStrRev[DESCRIPTION]," "))+1)
I have also used Rtrim and it doesnt work either

 

[John Spencer]

You need to Trim Description within the InStrRev call otherwise it is going
to always find the last space in the 30 character string
"Time to Quit " needs to be trimmed to "Time To Quit" before you
use InStrRev.

I think the following may do what you want.

Trim(Mid([Description],InStrRev(Trim([Description])," ")+1))
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

The Trim function only removes leading a training spaces from a string, so
it
is not appropriate for this situatuion. Additionally, there were some
extra
parentheses you don't need.

SortString: Mid([DESCRIPTION], InStrRev([DESCRIPTION]," ")+1)
--
Dave Hargis, Microsoft Access MVP

I meant to mention I am using this in an append query. I have a column to
append it to, but I am wondering if that is the problem

I am importing records with a fixed length description field. I need to
create a field from the last word of the description(everything to the
right
of the last space). The field length is 30. When I use the function
below --
it appears the trim function does not seem to shorten the string for
the
InStrRev function.

SortString: Mid(Trim([DESCRIPTION]),(InStrRev[DESCRIPTION]," "))+1)
I have also used Rtrim and it doesnt work either

 

[Klatuu]

Oh, yeah, fixed length!
Thanks, John. Note, however, the outer Trim isn't necessary

Mid([Description],InStrRev(Trim([Description])," ")+1)

is sufficient
--
Dave Hargis, Microsoft Access MVP

You need to Trim Description within the InStrRev call otherwise it is going
to always find the last space in the 30 character string
"Time to Quit " needs to be trimmed to "Time To Quit" before you
use InStrRev.

I think the following may do what you want.

Trim(Mid([Description],InStrRev(Trim([Description])," ")+1))
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

The Trim function only removes leading a training spaces from a string, so
it
is not appropriate for this situatuion. Additionally, there were some
extra
parentheses you don't need.

SortString: Mid([DESCRIPTION], InStrRev([DESCRIPTION]," ")+1)
--
Dave Hargis, Microsoft Access MVP

I meant to mention I am using this in an append query. I have a column to
append it to, but I am wondering if that is the problem


:

I am importing records with a fixed length description field. I need to
create a field from the last word of the description(everything to the
right
of the last space). The field length is 30. When I use the function
below --
it appears the trim function does not seem to shorten the string for
the
InStrRev function.

SortString: Mid(Trim([DESCRIPTION]),(InStrRev[DESCRIPTION]," "))+1)
I have also used Rtrim and it doesnt work either

 

[Klatuu]

Ignore my last post. The outer trim is necessary.
--
Dave Hargis, Microsoft Access MVP

You need to Trim Description within the InStrRev call otherwise it is going
to always find the last space in the 30 character string
"Time to Quit " needs to be trimmed to "Time To Quit" before you
use InStrRev.

I think the following may do what you want.

Trim(Mid([Description],InStrRev(Trim([Description])," ")+1))
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

The Trim function only removes leading a training spaces from a string, so
it
is not appropriate for this situatuion. Additionally, there were some
extra
parentheses you don't need.

SortString: Mid([DESCRIPTION], InStrRev([DESCRIPTION]," ")+1)
--
Dave Hargis, Microsoft Access MVP

I meant to mention I am using this in an append query. I have a column to
append it to, but I am wondering if that is the problem


:

I am importing records with a fixed length description field. I need to
create a field from the last word of the description(everything to the
right
of the last space). The field length is 30. When I use the function
below --
it appears the trim function does not seem to shorten the string for
the
InStrRev function.

SortString: Mid(Trim([DESCRIPTION]),(InStrRev[DESCRIPTION]," "))+1)
I have also used Rtrim and it doesnt work either