Solved

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

Posted on 2008-10-26
Medium Priority
418 Views
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?
0
• 3
• 2

LVL 10

Expert Comment

ID: 22809414
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
``````
0

Author Comment

ID: 22853515
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?
0

LVL 10

Accepted Solution

calpurnia earned 2000 total points
ID: 22855103
Copy and paste the code above into a module.

Change the first line to:
Public Function Convert2HTML(mystring)

Now create a query like this:
SELECT MyTable.Field1, Convert2HTML([Field1]) AS Expr1 FROM MyTable;
replacing MyTable with the name of your table and Field1 with the name of the field that contains the text you want converted.

Does this give you the desired output?

0

Author Closing Comment

ID: 31510150
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
0

LVL 10

Expert Comment

ID: 22858397
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 :)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
###### Suggested Courses
Course of the Month12 days, 22 hours left to enroll