Trim a field down

[Dennis Villareal]

i think this can be done. i would like to trim a field down that shows the
following

2" X 4" X 10 GA
4" X 2" X 3/16"
3-1/2" SQ X .210"
4-1/2" X 2-1/2" X 1/8"

there is many different texts, but the one thing they do have in common is
the last "X"

sometimes it is
5 X 2 X 3/16
sometimes it is
5 X 3/16

is there a way to tell it if 1 X exists trim from the X on if 2 X exists
trim from the second X on. this will be done in a query.

thanks

 

[KARL DEWEY]

I think you want your sample trimed to this --
2" X 4" X
4" X 2" X
3-1/2" SQ X
4-1/2" X 2-1/2" X

Try this --
Left([YourField], InStr([YourField], "X", InStr([YourField], "X")+1))

 

[John Spencer]

Take a look at the InstrRev function to find the last "X" in the field
and use that along with the LEFT function

LEFT([SomeField],InstrRev([SomeField],"X"))

Of course if there is NO X in the field or if the field value is null
then you are going to have an error. So you might want to check to see
if there is an X in the field's value.

IIF([SomeField] Like "*X*" ,LEFT([SomeField],InstrRev([SomeField],"X"))
,[SomeField])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================