Solved

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

Posted on 2008-10-26
5
349 Views
Last Modified: 2012-05-05
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
Comment
Question by:oldmillsaddlery
  • 3
  • 2
5 Comments
 
LVL 10

Expert Comment

by:calpurnia
Comment Utility
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

0
 

Author Comment

by:oldmillsaddlery
Comment Utility
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

by:
calpurnia earned 500 total points
Comment Utility
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

by:oldmillsaddlery
Comment Utility
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

by:calpurnia
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now