Trim data - Urgent

M

martyn

I want to trim the following type of data in a table field

AZTCK SEBCK
AZTCK UBSLCK
AZTCK WLBLONCK
AZTAE AZT57AAE
AZTAE RBILAE
AZTKD RBILKD
AZTJY ABNAMJY
AZTJY AZT57AJY

The answer I need is just the second part of the data but only when it
starts with AZT

Whats the easiest way to do this - some sort of trim function?
 
W

Wayne Morgan

I don't understand what you're wanting to "trim". Normally, trim will remove
extra spaces from the beginning and end of the data. Access will remove
trailing spaces automatically. Are you needing to removing preceding spaces?
Are you wanting to remove the space in the middle? Is the data you've
displayed all in one field or two?
 
M

martyn

Its all in one field. So I want the query to come back only with data
starting with AZT after the space.
So my example would give result:

AZT57AAE
AZT57AJY
 
D

Douglas J. Steele

SELECT Mid(Field1, InStr(Field1, " ") + 1) AS NewData
FROM MyTable
WHERE Field1 LIKE "* AZT*"

If you're using ADO rather than DAO, use % rather than * as the wild card
character.
 
L

Larry Daugherty

Hi,

You'll need to learn the use of some of the other string manipulation
functions. The first one is Mid$() where you'll determine the distance from
the start of the string to the first space. Or, if you are absolutely,
positively sure that the first part of the string including the space is
always the same number of characters you can reduce your target string by
that much: MyString=Right(MyString, len(MyString)-6). Next you'll use
Mid$() to determine if "AZT" is in the remaining string. If it's not,
you'll get a zero. If "AZT starts at position 1 you'll get a 1 and you'll
know you've got a hit. If you think it possible that the remaining string
might have trailing spaces that you don't want then MyString=Trim(Mystring).

HTH
 
Top