Remove all characters after "hyphen" in field

[sonofroy]

I have field in a query that values are in two parts example

location name 1 - material type
location name 2 - material type
location name 3 - material type

I would like to return in my query everything before the hyphen for example

location name 1
location name 2
location name 3

Thanks

 

[fredg]

I have field in a query that values are in two parts example

location name 1 - material type
location name 2 - material type
location name 3 - material type

I would like to return in my query everything before the hyphen for example

location name 1
location name 2
location name 3

Thanks

NewColumn:Left([FieldName],InStr([FieldName],"-")-2)

 

[Anthony Fontana]

Try this: Left$([textfield],InStr(1,[textfield],"-",1)-1)

Where textfield is the name of the field you want to search. If you know
that the "-" will always occur at location 17 (?) you can replace the entire
instr function with 17.

You might get errors listed if the text field doesn't have a "-"

 

[Krzysztof Naworyta]

Juzer Anthony Fontana <[email protected]> napisaÅ‚

| Try this: Left$([textfield],InStr(1,[textfield],"-",1)-1)
|
| Where textfield is the name of the field you want to search. If you
| know that the "-" will always occur at location 17 (?) you can replace
| the entire instr function with 17.
|
| You might get errors listed if the text field doesn't have a "-"

To avoid this error:

Left([textfield],InStr(1,[textfield] & "-","-",1)-1)

or:

RTrim(Left([textfield],InStr(1,[textfield] & "-","-",1)-1))