Error message in query?

[Guest]

I have a field named CityStateZip that shows the City Name, a comma, State
and Zip. i.e. Houston, TX 77478.

I used SQL as below:
Trim(Mid([04 Name address].[CityStateZip],1,InStr([04 Name
address].[CityStateZip],",")-1)) AS Cities, Right(Trim([04 Name
address].[CityStateZip]),Len(Trim([04 Name
address].[CityStateZip]))-InStr(1,[04 Name address].[CityStateZip],", ")) AS
State

However, I got a error message "Invalid procedure call". Please someone
helps and explain to me what did I do wrong? Thanks a million.

 

[Guest]

Try the following:

left ([04 Name address].[CityStateZip], InStr([04 Name
address].[CityStateZip],",")-1) AS Cities,
Mid ([04 Name address].[CityStateZip], InStr([04 Name
address].[CityStateZip],",") + 2, 2) As State

It's a little less complex (you don't have all of those TRIM() calls), and
if the strings are all as consistently formatted as you state, you can take
advantage of the fact that a State abbreviation is always only 2 characters.

You will also get the end of the string ("TX 77478") rather than just the
state abbrev, per specification of the RIGHT() function. If you actually just
want to split the string into a City and StateZip, then just take the last
'2' out of my MID() call.

I was unable to reproduce the problem, just cutting your SQL and pasting
into an SQL view.

Good Luck!

 

[tw]

where's the rest of the sql statement?

but you do have an error below. The () are incorrect in your right...
statement
try changing to

Right([04 Name address].[CityStateZip],Len(Trim([04 Name
address].[CityStateZip]))-InStr(1,[04 Name address].[CityStateZip],", ")) as
state

But this gives you the state and zip...
it might be better to do this

left(trim(Right(([04 Name address].[CityStateZip],Len(Trim(([04 Name
address].[CityStateZip]))-InStr(1,([04 Name address].[CityStateZip],",
"))),2) as state

there's a less complicated way to do this but this should get you started.