Trim Statement

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

Guest

I have a field in a table with City, State, Airport Code.

It looks like
Phoenix, AZ (PHX)
Tucson, AZ (TUS)

I am trying to break this up to separate out the City, State Airport Code.
So far I have the State and Airport Code separated out using the Trim
statement.

However I cannot get the City because it varies in length. I know for a fact
that there are 10 character spaces after the city but when I use the trim
statement it only gives me that in which I trim ie
(Format(trim(right([fieldname],8))) gives me AZ (PHX).

How do I trim the field but get the opposite of what I am trimming.

Hopefully this makes sense.
 
I have a field in a table with City, State, Airport Code.

It looks like
Phoenix, AZ (PHX)
Tucson, AZ (TUS)

I am trying to break this up to separate out the City, State Airport Code.
So far I have the State and Airport Code separated out using the Trim
statement.

However I cannot get the City because it varies in length. I know for a fact
that there are 10 character spaces after the city but when I use the trim
statement it only gives me that in which I trim ie
(Format(trim(right([fieldname],8))) gives me AZ (PHX).

How do I trim the field but get the opposite of what I am trimming.

Hopefully this makes sense.
Click to expand...

Trim doesn't do what you think it does: it merely removes leading and
trailing blanks. What you need instead is the InStr function to find
the position in the string of the comma:

City: Left([fieldname], InStr([fieldname], ",") - 1)
State: Mid([fieldname], InStr([fieldname], ",") + 2, 2)
AirportCode: Right([fieldname], 5)

or if you don't want the parentheses, and assuming all airport codes
are three letters long,

AirportCode: Mid([fieldname], InStr([fieldname], "(") + 1, 3)

John W. Vinson[MVP]
 
Back
Top