Solved

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

Posted on 2008-10-26
349 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

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

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 500 total points
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

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

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.