Solved

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

Posted on 2008-10-26
5
385 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 10

Expert Comment

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

Open in new window

0
 

Author Comment

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

by:
calpurnia earned 500 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

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

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

749 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