TRIM function with fixed length field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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


JoeA2006 said:
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


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

Klatuu said:
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


JoeA2006 said:
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


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


John Spencer said:
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
..

Klatuu said:
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


JoeA2006 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Ignore my last post. The outer trim is necessary.
--
Dave Hargis, Microsoft Access MVP


John Spencer said:
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
..

Klatuu said:
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


JoeA2006 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top