If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Trim a field down
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 |
#2
|
|||
|
|||
Trim a field down
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)) "Dennis Villareal" wrote: 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 |
#3
|
|||
|
|||
Trim a field down
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 '================================================= === Dennis Villareal wrote: 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 |
Thread Tools | |
Display Modes | |
|
|