ArcMap, Field Calculator, & VB

[Clix]

Hello
I was trying to move data from one field to another in an ArcMap 10 layer using Field Calculator and ran into problems. I'm sure it is a syntax thing as I am not that well versed in VB, but Google failed me so I turn to you.

Data in field I'll be moving is formated as:

9500 Lowland Woods Blvd, Any City, Fl 99999

How I want it to look in the new field is:

9500 Lowland Woods Blvd

My first pass was to use LEFT with Instr to identify the limit. I put into Field Calculator:

Code: Select all

Left( [CrisisAddr], InStr( [CrisisAddr],","))

It returned:

9500 Lowland Woods Blvd,

As you can see it brought the comma over with it. I wanted to see if I could add a -1 to the InStr function.

InStr( [CrisisAddr],",") gave me a value of 25
InStr( [CrisisAddr],",")-1 gave me a value of 24

so far so good

But when I tried to incorporate that into the Field Calculator statement it errors.

Code: Select all

Left([CrisisAddr],( InStr( [CrisisAddr],",")-1))

Error statement: Invalid Procedure call or argument "Left"

I know it has to be an syntax error or abuse of the parans, but for the life of me I cannot figure it out. It's an academic question now as I did find a work around.

Spoiler:

Code: Select all

Replace( [Address],",","")

But I would really like to know if it is possible to do this and what the syntax is.

Just for the record I tried this also:

Spoiler:

prelogic script code

Code: Select all

DIM RESULT
DIM ADDRESS

RESULT=InStr( [CrisisAddr],",")-1
ADDRESS=Left( [CrisisAddr], RESULT)

Code: Select all

Address=Address

If anyone could point out my ridiculously simple error I would appreciate it.

[Soupspoon]

It is far too long since I touched a VB-alike language (and then it was in the form of LotusScript, probably), so do not know for sure if I'm proficient enough with this particular dialect (save that it's readable much like all the rest), but I'll try a quick suggestion in leiu of anyone else having yet posted.

I am first of all assuming that you don't need to do something like:

Code: Select all

… Subtract( InStr( [CrisisAddr],","), 1)…

...or maybe "Add( <FOO>, -1)", so the obvious yet normally insanely inconsequential difference between your working(ish) and non-working Left() function is that in every function call except the complaining one has a space between the "FunctionName(" and its parameters...

Thus, is it possible that instead of:

Code: Select all

Left([CrisisAddr],( InStr( [CrisisAddr],",")-1))

You need..?

Code: Select all

Left( [CrisisAddr],( InStr( [CrisisAddr],",")-1))

(Correction, your spoilered version doesn't have that failing, assuming everything else is equal and well-copied. But you can at least check if RESULT being created without the -1 works as per original comma-retaining version, maybe identify some rather awkard type mismatching between position number returned from the InStr( …) and a literal 1 that the subtraction operator can't overload and/or return from correctly.)

Just to check your other logic, though, it seems well nested to me.

Code: Select all

Left(
     [CrisisAddr]
     ,
     (
      InStr(
            [CrisisAddr]
            ,
            ","
      ) // Instr( [],"") looks Ok, with presumed legal var format and string literal as parameters
      -
      1
     ) // ( #-#) looks Ok, a bare parenthisised (necessarily so?) two numbers mathematically operated upon
) // Left( [], #) looks Ok, a var format and a numeric


...but how strict is the interpreter to types? That's really my basic (NPI!) fall-back answer, even though it has all the appearance of an environment that doesn't really care as long as you don't try to use a blatently non-numeric string for a numeric param.

[Clix]

Thanks for the reply! Right now my ArcMap is tied up processing a new street layer, but when I get a chance I'll try your suggestions and report back.