Text Manipulation Help wanted Instr and Replace

B

bobdydd

Hi

Access 2007

I have the following (or similar) text in Memo Field
that is called txtDescription2

<p>
<div align="justify">
<UL TYPE="DISC">
<li> Maximum 250 bar (3625 psi) pressure
<li> Maximum water flow 18.2 litres per minute
<li> 13Hp Petrol engine
<li> Professional lance with protective shroud
<li> Detergent pick-up hose.
</ul>
</div>
</p>

I have 5 other fields that I would like to transfer
the above data into;
txtbullet-point1
txtbullet-point2
txtbullet-point3
txtbullet-point4
txtbullet-point5

So that it reads:
Maximum 250 bar (3625 psi) pressure
Maximum water flow 18.2 litres per minute
13Hp Petrol engine
Professional lance with protective shroud
Detergent pick-up hose.


I have tried with Instr and Replace

Can anyone help

Thanks and Best Regards
 
A

Albert D. Kallal

"bobdydd" wrote in message
I have 5 other fields that I would like to transfer
the above data into;
txtbullet-point1
txtbullet-point2
txtbullet-point3
txtbullet-point4
txtbullet-point5
Click to expand...

It not clear what you mean or want by text bullets?

However, if you take the raw data from a rich text field as above, you can
plain text it with the new plaintext command introduced in 2007.

try this:

msgbox PlainText(rstRec!Notes)


The above msgbox will produce what you have now and the above "new" command
was introduced for use with the rich text support in 2007.

So all of the rich text part formatting can be pulled out with above new
command in Access called PlainText() and you not need to do a bunch of text
parsing and have to use "Instr()" etc.
 
B

bobdydd

Thanks for looking at this Albert.
I probably havent explained myself very well

The fields txtbullet-point1 to 5 are empty fields at the moment
and I would like to take each of the 5 lines that start with <li>
in the plain text memo field called txtDescription2
and place them in one of the txtbullet-point fields

Best Regards
 
B

Bob Barrows

My advice would be:
stop right there and rethink this!

You're committing spreadsheet. This is a relational database, not a
spreadsheet. Think narrow and long, not short and wide.
What happens when the users decide they need to add 6 or more bullet points?
With a spreadsheet, it's a simple matter to put "bullet point 6" in the
first cell of the next column and off you go. With a database, adding an
extra field is just the start of the work. You now have the task of
modifying every query that retrieves data from this table, every form and
report, every bit of code that writes data into it. Silly!

Create a related table called "BulletPoints" with the key fields to identify
the record the bullet points refer to, a field to number the points, and a
field to contain the bullet-point text. With this design, each bullet point
is in its own record, and you have no more limitation on the number of
bullet points stored.

As for parsing the text in the memo field, it sounds like you are tring to
do that with an expression. I don't think you will have any success with
that. You need to create a VBA subroutine that uses Mid() and Instr() to
isolate the substring containing the bullet points based on the locations of
the two ul tags, then Split() to break the string up into an array
containing each bullet point, and then loops through the array to write the
text into the table. Give it a try and if you have trouble, get back to us.
 
T

The Frog

You could parse the text through a regular expression with capture
groups. It's very fast and efficient.

The Frog
 
B

Bob Barrows

The said:
You could parse the text through a regular expression with capture
groups. It's very fast and efficient.

The Frog
Click to expand...

True, but few VBA users (including myself, and I suspect, the OP) are fluent
enough with regex to make it worthwhile to suggest, IMO.
 
B

bobdydd

Thanks guys for having a look but I think I'm beaten on this one.

Back to the drawing board
 
B

Bob Barrows

bobdydd said:
Thanks guys for having a look but I think I'm beaten on this one.
Click to expand...

At what point?

If you need more of a start, try this:

Public Sub ParseBullets(s As String)
Dim sBullets As String
Dim aBullets As Variant

'First isolate the bullets substring
sBullets = Trim(Mid(s, InStr(s, "<UL TYPE=""DISC"">") + 16))
sBullets = Left(sBullets, InStr(sBullets, "</ul>") - 1)

aBullets = Split(sBullets, "<li>")
For i = 0 To UBound(aBullets)
Debug.Print Trim(aBullets(i))
Next i

End Sub

Are you expecting someone to write the entire procedure for you? If so, we
need more information. The name of the table would do for starters. Then we
need the names of the key field(s) in the table, and the names of the
controls on your form that are linked to those key fields.
 
Top