Return multiple characters within a text string

  • Thread starter Thread starter broncojim
  • Start date Start date
B

broncojim

I have a table with a field that stores the type of a particular asset. I
want to return only part of the text string in a query. There are roughly
100 asset types. Some examples are below:

A1D 16S
CENTRON 2S
J4S 05S
KV2c 16K E

I want to retun the part of the text string like "16S" or "2S" or "05S" or
"16K".

Any help would be greatly appreciated.
 
broncojim said:
I have a table with a field that stores the type of a particular asset. I
want to return only part of the text string in a query. There are roughly
100 asset types. Some examples are below:

A1D 16S
CENTRON 2S
J4S 05S
KV2c 16K E

I want to retun the part of the text string like "16S" or "2S" or "05S" or
"16K".
Click to expand...


See if this can do what you want:

Split(thefield," ")(1)
 
Marshall,

I typed this into the query:

Split([ASSET_TYPE]," ")(1)

and got the following error:

The expression you entered has an invalid dot or operator or parentheses.

Jim
 
Bob,

The data is not inconstent, rather the E designates an asset with a special
feature that the regular ... 16K asset does not have. Yeah, I have racked my
brain to figure this one out.
 
Bob,

How would the formula in the query be written?

I tried [ASSET_TYPE] mid(x, instr(x, " ") + 1)

Got an error
 
broncojim said:
I typed this into the query:

Split([ASSET_TYPE]," ")(1)

and got the following error:

The expression you entered has an invalid dot or operator or parentheses.
Click to expand...


Arrrggghhh, queries don't understand the array index syntax.
Sorry.

You could create a little function ib a standard module:

Public Function ExtractPart(part)
ExtractPart = Split(part," ")(1)
End Function

Then the query can just call the function:

MyField: ExtractPart(thefield)
 
Back
Top