Help with InStr function

[Lez]

I have a feild in my table that contains a bulleted list of items in html
format. However, I need to split the bullet points into 5 seperate fields and
have been trying to complete this using the InStr function

This first part works:

bullet1: Left([HTML Description],(InStr(1,[HTML Description],"</li>"))-1)

However, when I use this modified version for the 2nd bullet point it just
gives me the first value again?

bullet2: Left([HTML Description],(InStr(2,[HTML Description],"</li>"))-1)

If I use the 'mid' rather than 'left' it give me the 2nd bullet point, but
also all the text after it, which is not what I want.

Can anyone help with a method to sort this please

Regards

 

[John Spencer]

The first argument to INSTR specifies the starting point for the search. It
doesn't specify which incidence to return.

You might use some vba code to get the various segments.

Parse string into sections and get a specific section / Item / token from the
string/

Paste the function below into a VBA module and save it. Make sure the name of
the module is NOT the same as the name of this or any other procedure.


In your query you would use it like
Field: Bullet1: fGetToken([HTML Description],"</li>",1)

Field2: Bullet2: fGetToken([HTML Description],"</li>",2)

'================= FUNCTION Starts =====================
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
'================= FUNCTION ENDS =====================

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

 

[Allen Browne]

Try the Split() function in code, e.g.:
Split([HTML Description], "<li>")(1)

You may need to create a wrapper function to get this to work, rather than
try it directly in the query. And strip the end-tag with Left() and Len().

 

[Lez]

Cheers John,

Many thanks for that, perfect answer

The first argument to INSTR specifies the starting point for the search. It
doesn't specify which incidence to return.

You might use some vba code to get the various segments.

Parse string into sections and get a specific section / Item / token from the
string/

Paste the function below into a VBA module and save it. Make sure the name of
the module is NOT the same as the name of this or any other procedure.


In your query you would use it like
Field: Bullet1: fGetToken([HTML Description],"</li>",1)

Field2: Bullet2: fGetToken([HTML Description],"</li>",2)

'================= FUNCTION Starts =====================
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
'================= FUNCTION ENDS =====================

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

I have a feild in my table that contains a bulleted list of items in html
format. However, I need to split the bullet points into 5 seperate fields and
have been trying to complete this using the InStr function

This first part works:

bullet1: Left([HTML Description],(InStr(1,[HTML Description],"</li>"))-1)

However, when I use this modified version for the 2nd bullet point it just
gives me the first value again?

bullet2: Left([HTML Description],(InStr(2,[HTML Description],"</li>"))-1)

If I use the 'mid' rather than 'left' it give me the 2nd bullet point, but
also all the text after it, which is not what I want.

Can anyone help with a method to sort this please

Regards