Return multiple characters within a text string

[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.

 

[Marshall Barton]

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".

See if this can do what you want:

Split(thefield," ")(1)

 

[raskew via AccessMonster.com]

Well, it appears your data may be somewhat inconsistent, e.g. J4S 05S where
you want to return 05S, and KV2c 16K E, where you want to return 16K (but not
16K E). This, and other deviations, is going to pose a problem.

If you want to return 05S from J4S 05S, this will do it:
x = "J4S 05S"

? mid(x, instr(x, " ") + 1)
05S

...but, the same logic

x = "KV2c 16K E"
? mid(x, instr(x, " ") + 1)
16K E

You'll need to give some thought to that.

Bob

 

[broncojim]

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

 

[broncojim]

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.

 

[broncojim]

Bob,

How would the formula in the query be written?

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

Got an error

 

[Marshall Barton]

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.

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)

 

[raskew via AccessMonster.com]

In my example, I used a variable (x) and populated with a value. Reason: to
avoid having to
to write out long field names, such as [ASSET_TYPE].
So, if you were goiing that route, something like:

dim x as string
x = [ASSET_TYPE]
? mid(x, InStr(x, " ") + 1)

Bob

Bob,

How would the formula in the query be written?

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

Got an error

Well, it appears your data may be somewhat inconsistent, e.g. J4S 05S where
you want to return 05S, and KV2c 16K E, where you want to return 16K (but not

[quoted text clipped - 29 lines]

 

[raskew via AccessMonster.com]

This function, copied to a standard module, will allow you to extract a word
or group of characters from a string, based on a designated position and a
designated delimiter.

To use, copy the function to a standard module. Save the module, ensuring
the module name is not the same as the function name. Call as shown in the
example:

Public Function PassbackAnyword(pstrText As String, pintword As Integer,
pstrdivider As String) As Variant
'Purpose: Given a string, a word and the divider,
' returns the specific word.
'Input: ? PassbackAnyword("1234*7890*8888", 2, "*")
'Output: 7890

Dim intLoop As Integer
Dim intPos As Integer
Dim intprev As Integer
Dim varstring As Variant

'Don't waste your time if the divider isn't in the string
If InStr(pstrText, pstrdivider) <> 0 Then

intPos = 1
intprev = 1

pstrText = pstrText & pstrdivider
For intLoop = 1 To pintword

intPos = InStr(intprev + 1, pstrText, pstrdivider)

If intPos <> 0 Then
If intLoop < pintword Then
intprev = intPos
End If
Else
intPos = intprev

End If

Next

varstring = Mid(pstrText, intprev, intPos - intprev)

If pintword > 1 And varstring <> "" Then
varstring = Right(varstring, Len(varstring) - 1)
End If
Else
'If it's the first word we want then it's all the string otherwise is
nothing
If pintword = 1 Then
varstring = pstrText
End If
End If
If varstring = "" Or varstring = pstrdivider Then
varstring = Null
End If

PassbackAnyword = varstring
End Function

HTH - Bob

In my example, I used a variable (x) and populated with a value. Reason: to
avoid having to
to write out long field names, such as [ASSET_TYPE].
So, if you were goiing that route, something like:

dim x as string
x = [ASSET_TYPE]
? mid(x, InStr(x, " ") + 1)

Bob

Bob,

[quoted text clipped - 9 lines]