InStr Function Help

[Lez]

I Guys have a function that allows me to find the part of a feild and create
a new field from this value, However, can anyone tell me how I get it to just
find the first value "Sizes :" and ingnore the next part "Colours :" I need
to create a seperate field for colours. This is the call and the function is
below.

Sizes: fGetToken([style],"Sizes :",2)


Public Function fGetToken(strIn, _
Optional strDelimiter As String = " ", _
Optional LPos As Long = 1)
'Return the Nth item from a delimited list of items.

Dim strArr As Variant

If Len(strIn & "") = 0 Then
fGetToken = strIn
Else
strArr = Split(strIn, strDelimiter)
If LPos - 1 <= UBound(strArr) Then
fGetToken = strArr(LPos - 1)
Else
fGetToken = Null
End If
End If

End Function

 

[John Spencer]

Perhaps you can nest the call

fGetToken(fGetToken([style],"Sizes :",2),"Colours",1)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Lez]

Hi John,

Many thanks for that, and yes nesting does work and had been able to create
this by making multiple selections then doing an update query to create the
final table.

This is time consuming and means a fair amount of intervention, so what I
would like is a method that looks for ALL "size" values regardless of
position in the field and ALL "Colours"

The problem is that "Size" is sometimes first in the field and then
sometimes "Colour" is the first, so what I need is a way to simply look for
Size regardless of it's position in the field and assume I need to delimiter
for the the value I need to store.

Example of field values:

Colour: Black/Green / Size : Small: Chest 37-38 Inches /
Size : XL 44 46 Inch Chest / Colour: Blue With Black Trim /

As you can see to complicate the situation the colours can also have a /
within the value which makes that no good to use as a delimiting character

If you have any suggestions I would appreciate your help.

Regards

Perhaps you can nest the call

fGetToken(fGetToken([style],"Sizes :",2),"Colours",1)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I Guys have a function that allows me to find the part of a feild and create
a new field from this value, However, can anyone tell me how I get it to just
find the first value "Sizes :" and ingnore the next part "Colours :" I need
to create a seperate field for colours. This is the call and the function is
below.

Sizes: fGetToken([style],"Sizes :",2)


Public Function fGetToken(strIn, _
Optional strDelimiter As String = " ", _
Optional LPos As Long = 1)
'Return the Nth item from a delimited list of items.

Dim strArr As Variant

If Len(strIn & "") = 0 Then
fGetToken = strIn
Else
strArr = Split(strIn, strDelimiter)
If LPos - 1 <= UBound(strArr) Then
fGetToken = strArr(LPos - 1)
Else
fGetToken = Null
End If
End If

End Function

 

[John Spencer]

That is a mess and will require lots of work

IF you always have exactly the structure - attribute + space + colon - you
could build a vba function to handle that. Something like the following might
work. You would call it with

fGetAttribute([Your string],"Colour :")

You might need to use ; instead of , as the separator.

Public Function fGetAttribute(strIn, strAttribute)
'
Dim iPos As Long
Dim strReturn As String

iPos = InStr(1, strIn & "", strAttribute)
If iPos = 0 Then
fGetAttribute = Null 'Attribute does not exist
Else
'Trim off all the stuff before the attribute we are
'looking for
strReturn = Mid(strIn, iPos + Len(strAttribute) + 1)
iPos = InStr(1, strReturn, " :") 'Find the next colon
If iPos = 0 Then
fGetAttribute = strReturn
Else
strReturn = Left(strReturn, iPos - 2)
iPos = InStrRev(strReturn, " ")
strReturn = Left(strReturn, iPos - 1)
fGetAttribute = strReturn
End If

End If

End Function


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

Many thanks for that, and yes nesting does work and had been able to create
this by making multiple selections then doing an update query to create the
final table.

This is time consuming and means a fair amount of intervention, so what I
would like is a method that looks for ALL "size" values regardless of
position in the field and ALL "Colours"

The problem is that "Size" is sometimes first in the field and then
sometimes "Colour" is the first, so what I need is a way to simply look for
Size regardless of it's position in the field and assume I need to delimiter
for the the value I need to store.

Example of field values:

Colour: Black/Green / Size : Small: Chest 37-38 Inches /
Size : XL 44 46 Inch Chest / Colour: Blue With Black Trim /

As you can see to complicate the situation the colours can also have a /
within the value which makes that no good to use as a delimiting character

If you have any suggestions I would appreciate your help.

Regards

Perhaps you can nest the call

fGetToken(fGetToken([style],"Sizes :",2),"Colours",1)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I Guys have a function that allows me to find the part of a feild and create
a new field from this value, However, can anyone tell me how I get it to just
find the first value "Sizes :" and ingnore the next part "Colours :" I need
to create a seperate field for colours. This is the call and the function is
below.

Sizes: fGetToken([style],"Sizes :",2)


Public Function fGetToken(strIn, _
Optional strDelimiter As String = " ", _
Optional LPos As Long = 1)
'Return the Nth item from a delimited list of items.

Dim strArr As Variant

If Len(strIn & "") = 0 Then
fGetToken = strIn
Else
strArr = Split(strIn, strDelimiter)
If LPos - 1 <= UBound(strArr) Then
fGetToken = strArr(LPos - 1)
Else
fGetToken = Null
End If
End If

End Function