asked on

Convert text held in access table to html with bullet points and store in access table

Convert text held in access table to html with bullet points and store in access table. The text has * where the bullet points should start. These need to be replaced with a bullet point and saved to new field in the database.

I need to upload this as a product description to our website and because of google spiders I prefer to have the html created before the upload
Here is an example text

Designed to be hung in the stable. *Add Lik-its to the top section and Little Lik-its to the ball. *Will keep the horse interested as he searches for a tasty reward.

This is the way it should look on the website

Designed to be hung in the stable.
-Add Lik-its to the top section and Little Lik-its to the ball.
-Will keep the horse interested as he searches for a tasty reward.

Could a query handle this?

calpurnia

You'd need to write a function which could then be used in the query.

Try the code below, and include the function in your query like this:

SELECT MyTable.Field1, Convert2HTML([Field1]) AS Expr1
FROM MyTable;


Function Convert2HTML(mystring)
 
Dim newstring As String
Dim nextpos As Integer
Dim nextpos2 As Integer
 
newstring = "<p>"
 
nextpos = InStr(1, mystring, "*")
If nextpos = 0 Then
    newstring = newstring & mystring & "</p>"
Else
    newstring = newstring & Mid(mystring, 1, nextpos - 1) & "<ul>"
    Do While nextpos <> 0
        nextpos2 = InStr(nextpos + 1, mystring, "*")
        If nextpos2 = 0 Then
            newstring = newstring & "<li>" & Mid(mystring, nextpos + 1)
            Exit Do
        Else
            newstring = newstring & "<li>" & Mid(mystring, nextpos + 1, nextpos2 - nextpos - 1)
        End If
        nextpos = nextpos2
    Loop
    newstring = newstring & "</ul></p>"
End If
        
Convert2HTML = newstring
 
End Function

Open in new window

oldmillsaddlery

ASKER

Hi Sorry been away for week thanks for the post . I am a bit lost here. Where you say write function can you explain in more detail?

ASKER CERTIFIED SOLUTION

calpurnia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

oldmillsaddlery

ASKER

This is the perfect solution and has saved me much time, I am delighted with the solution and the way you handled it. I feel rather guilty that I have not paid you directly for this

calpurnia

I'm glad I could help! By the way, the points you awarded me have resulted in me getting my first EE certification - Master in the MS Access zone :)