?
Solved

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

Posted on 2008-10-26
5
Medium Priority
?
405 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 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

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

771 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