parse or format data from memo field onto Access report

[RMC]

Hello,

I'm looking for a way to parse/format a memo field within a report.

The Access 2000 database (application) has an equipment table that
holds a memo field. Within the report, the memo field is printed within
the detailed area. The problem is, the apllication is not setup
properly, thus the users are entering data within the memo field as:

location1 1/1/2005 1/1/2006
location2 1/1/2006 5/1/2006
location3 6/1/2006 11/9/2006
location4 11/10/2006 11/18/2006
location5 11/19/2006 11/21/2006
location6 11/22/2006 12/01/2006
location7 12/02/2006 12/31/2006

In between the data, there are 2 tabs and at the end a return key code.
When you look into the database memo field, there are 2 squares in
between the location and dates.

Can this be parsed out properly into seperate text boxes within the
report or can I just format it
within a memo box on the report.

the report has the memo filed on it though displys the data bunchedup
with 2 squares in between the location and dates.

I basically need a clean way on how to do this; either by code, format
or any other suggestions.

Any info would be appreciated.

Ron Mck

[Larry Linson]

You probably aren't going to be happy when I say that Memo Fields are
intended for unformatted text; that Location and the two dates should be
separate Fields. But, even to revise the design, you'd need at least a
semi-automated way to separate the parts.

You need to determine what the "black squares" represent -- that is a common
way that non-printable characters are printed in many typefaces/fonts. They
are, most likely, control characters. A "new line" in ASCII text is Chr$(13)
& Chr$(10) which represent Carriage Return and Line Feed characters -- if
that's what you had, chances are, it would just begin a new line, so my
guess is that they are not those characters, in that order.

Without knowing details, we can't offer a good suggestion. If the fields you
describe as "location1..." are fixed length, if the dates are fixed length,
e.g., 01/01/2006, not 1/1/2006, sometimes, then the work would be simpler.
If not, VBA code will have to try to determine where "subfields" in the Memo
Field begin and end... by whatever is represented by the represented by
those "black squares", text, and spaces.

You also need to determine what _else_ is in the Memo Field, and how you
want to handle it -- if it is unformatted text, you can just put it back in
the Memo Field after you strip off the Location and Dates.

So, if you can clarify with some detail, someone may be able to offer useful
suggestions.

Larry Linson
Microsoft Access MVP
"RMC" <Mc*************@hotmail.comwrote in message
news:11*********************@m73g2000cwd.googlegro ups.com...

Hello,

I'm looking for a way to parse/format a memo field within a report.

The Access 2000 database (application) has an equipment table that
holds a memo field. Within the report, the memo field is printed within
the detailed area. The problem is, the apllication is not setup
properly, thus the users are entering data within the memo field as:

location1 1/1/2005 1/1/2006
location2 1/1/2006 5/1/2006
location3 6/1/2006 11/9/2006
location4 11/10/2006 11/18/2006
location5 11/19/2006 11/21/2006
location6 11/22/2006 12/01/2006
location7 12/02/2006 12/31/2006

In between the data, there are 2 tabs and at the end a return key code.
When you look into the database memo field, there are 2 squares in
between the location and dates.

Can this be parsed out properly into seperate text boxes within the
report or can I just format it
within a memo box on the report.

the report has the memo filed on it though displys the data bunchedup
with 2 squares in between the location and dates.

I basically need a clean way on how to do this; either by code, format
or any other suggestions.

Any info would be appreciated.

Ron Mck

[Bob Quintal]

"RMC" <Mc*************@hotmail.comwrote in
news:11*********************@m73g2000cwd.googlegro ups.com:

Hello,

I'm looking for a way to parse/format a memo field within a
report.

The Access 2000 database (application) has an equipment table
that holds a memo field. Within the report, the memo field is
printed within the detailed area. The problem is, the
apllication is not setup properly, thus the users are entering
data within the memo field as:

location1 1/1/2005 1/1/2006
location2 1/1/2006 5/1/2006
location3 6/1/2006 11/9/2006
location4 11/10/2006 11/18/2006
location5 11/19/2006 11/21/2006
location6 11/22/2006 12/01/2006
location7 12/02/2006 12/31/2006

In between the data, there are 2 tabs and at the end a return
key code. When you look into the database memo field, there
are 2 squares in between the location and dates.

Can this be parsed out properly into seperate text boxes
within the report or can I just format it
within a memo box on the report.

the report has the memo filed on it though displys the data
bunchedup with 2 squares in between the location and dates.

I basically need a clean way on how to do this; either by
code, format or any other suggestions.

Any info would be appreciated.

Ron Mck

That's what happens when using a memo field instead of a child
table. The solution I suggest will involve creating the child
table with four or five fields and replacing the memo textbox
with a sub-report. You might as well modify the database and the
entry forms at the same time, since the problem will only get
worse as the number of entries grows.
Note that the black squares represent a tab character (ascii
value 8) between columns, and possibly a new line character
(ascii 10) or Carriage return (ascii 13) at the end of each
line.

Write a visual basic sub procedure that stores the whole memo
field to a variable. Then the module uses the instr() function
to take the text to the left of the first tab, stores that to
another variable, and uses the mid() function to remove the
extracted part from the memo's variable. Repeat till you've done
one row. Write the variables for each part and the Identifier
(equipment ID?) that identifies the correct row in the table
from which the data belongs.
repeat till you've done the whole memo field. Repeat till you've
done the table.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[RMC]

I though it was quitre clear, though. I stated in between the loction
and dates, they are Tabs(Tab key pressed twice) and aty the end of the
line a cariage retuern key.

example within the memo field:

location1<TAB><TAB>1/1/2005<TAB><TAB>1/1/2006<RETURN>
location2<TAB><TAB>1/1/2006<TAB><TAB>5/1/2006<RETURN>
location3<TAB><TAB>6/1/2006<TAB><TAB>11/9/2006<RETURN>
location4<TAB><TAB>11/10/2006<TAB><TAB>11/18/2006<RETURN>
location5<TAB><TAB>11/19/2006<TAB><TAB>11/21/2006<RETURN>
location6<TAB><TAB>11/22/2006<TAB><TAB>12/01/2006<RETURN>
location7<TAB><TAB>12/02/2006<TAB><TAB>12/31/2006<RETURN>
I can work with fixed date lenghs such as 01/01/2006, thus this is a
good suggestion and the data can be changed. I understand your point,
this is not at all good way of doing it, though the developer of the
application may be looking into a change in the future, this is all I
have to work with to create an MS Access report.

For your other question about what else would vbe in the memo field,
This would be all.

I just need some help on coding this to either display the memo field
on the report like this:

location1 1/1/2005 1/1/2006
location2 1/1/2006 5/1/2006
location3 6/1/2006 11/9/2006
location4 11/10/2006 11/18/2006
location5 11/19/2006 11/21/2006
location6 11/22/2006 12/01/2006
location7 12/02/2006 12/31/2006

or within seperate text boxes on the report. (basically parse/extract
the data seperately)

I appreciate any help or comments,

Ron

Larry Linson wrote:

You probably aren't going to be happy when I say that Memo Fields are
intended for unformatted text; that Location and the two dates should be
separate Fields. But, even to revise the design, you'd need at least a
semi-automated way to separate the parts.

You need to determine what the "black squares" represent -- that is a common
way that non-printable characters are printed in many typefaces/fonts. They
are, most likely, control characters. A "new line" in ASCII text is Chr$(13)
& Chr$(10) which represent Carriage Return and Line Feed characters -- if
that's what you had, chances are, it would just begin a new line, so my
guess is that they are not those characters, in that order.

Without knowing details, we can't offer a good suggestion. If the fields you
describe as "location1..." are fixed length, if the dates are fixed length,
e.g., 01/01/2006, not 1/1/2006, sometimes, then the work would be simpler.
If not, VBA code will have to try to determine where "subfields" in the Memo
Field begin and end... by whatever is represented by the represented by
those "black squares", text, and spaces.

You also need to determine what _else_ is in the Memo Field, and how you
want to handle it -- if it is unformatted text, you can just put it back in
the Memo Field after you strip off the Location and Dates.

So, if you can clarify with some detail, someone may be able to offer useful
suggestions.

Larry Linson
Microsoft Access MVP
"RMC" <Mc*************@hotmail.comwrote in message
news:11*********************@m73g2000cwd.googlegro ups.com...

Hello,

I'm looking for a way to parse/format a memo field within a report.

The Access 2000 database (application) has an equipment table that
holds a memo field. Within the report, the memo field is printed within
the detailed area. The problem is, the apllication is not setup
properly, thus the users are entering data within the memo field as:

location1 1/1/2005 1/1/2006
location2 1/1/2006 5/1/2006
location3 6/1/2006 11/9/2006
location4 11/10/2006 11/18/2006
location5 11/19/2006 11/21/2006
location6 11/22/2006 12/01/2006
location7 12/02/2006 12/31/2006

In between the data, there are 2 tabs and at the end a return key code.
When you look into the database memo field, there are 2 squares in
between the location and dates.

Can this be parsed out properly into seperate text boxes within the
report or can I just format it
within a memo box on the report.

the report has the memo filed on it though displys the data bunchedup
with 2 squares in between the location and dates.

I basically need a clean way on how to do this; either by code, format
or any other suggestions.

Any info would be appreciated.

Ron Mck

[RMC]

Thank's for the info, I've started working on a sub report.
Unfortunatly not myprogram (and not written in MS Access). The data
file is MS Access, thus for now I need to retract the info given.

Can you assist alittle more with code that will allow me to do this?

Any info would be appreciated,

Ron

Bob Quintal wrote:

That's what happens when using a memo field instead of a child
table. The solution I suggest will involve creating the child
table with four or five fields and replacing the memo textbox
with a sub-report. You might as well modify the database and the
entry forms at the same time, since the problem will only get
worse as the number of entries grows.
Note that the black squares represent a tab character (ascii
value 8) between columns, and possibly a new line character
(ascii 10) or Carriage return (ascii 13) at the end of each
line.

Write a visual basic sub procedure that stores the whole memo
field to a variable. Then the module uses the instr() function
to take the text to the left of the first tab, stores that to
another variable, and uses the mid() function to remove the
extracted part from the memo's variable. Repeat till you've done
one row. Write the variables for each part and the Identifier
(equipment ID?) that identifies the correct row in the table
from which the data belongs.
repeat till you've done the whole memo field. Repeat till you've
done the table.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Rick Brandt]

RMC wrote:

I though it was quitre clear, though. I stated in between the loction
and dates, they are Tabs(Tab key pressed twice) and aty the end of the
line a cariage retuern key.

Standard Access controls cannot display the Tab character.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Larry Linson]

"RMC" <Mc*************@hotmail.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...

>I though it was quitre clear, though. I stated in between the loction
and dates, they are Tabs(Tab key pressed twice) and aty the end of the
line a cariage retuern key.

Sorry, I overlooked the part about the Tabs.

The following Function procedures, if placed in a Standard Module, could be
called from within the Query you use as Record Source for your Report, or
elsewhere, if you prefer -- each stands alone, and does not rely on the
others. At the cost of reduced readability, they could be shortened somewhat
(but my preference is for readability over minimal/questionable improvement
in "efficiency").

Function ParseLoc(pstrMemo As String) As String
ParseLoc = Left(pstrMemo, InStr(1, pstrMemo, vbTab & vbTab) - 1)
End Function

Function ParseFirstDate(pstrMemo As String) As String
Dim intDateStart As Integer, intDateStop As Integer
intDateStart = InStr(1, pstrMemo, vbTab & vbTab) + 2
intDateStop = InStr(intDateStart, pstrMemo, vbTab) - 1
ParseFirstDate = Mid(pstrMemo, intDateStart, intDateStop - intDateStart +
1)
End Function

Function ParseSecondDate(pstrMemo As String) As String
Dim intTwoTab As Integer
Dim intDateStart As Integer
Dim intDateStop As Integer
intTwoTab = InStr(1, pstrMemo, vbTab & vbTab) + 2
intDateStart = InStr(intTwoTab, pstrMemo, vbTab & vbTab) + 2
intDateStop = InStr(intDateStart, pstrMemo, vbCr) - 1
ParseSecondDate = Mid(pstrMemo, intDateStart, intDateStop)
End Function

And, of course, you may wish to add some error handling.

Larry Linson
Microsoft Access MVP

[RMC]

Thank's, I've started incorporating this into my function and i'ts
working descent. I've decided to create a table and have the memo field
data extracted with the ID. The only issue I'm having is that, it
populated the table with the right amount of record info though only
using the first line of the memo data. Other works, if one ID has4
lines within the memo field, it repeats the data in the newly created
table with the first line of memo info 4 times.

Also, would it be possible to run this within a SQl statement for a
report and not bother with a table creation?

Her is the code:

Sub FixData()

Dim strMemotxt As String
Dim rstEquipment As Recordset ' our main table
Dim rstEquipmentLocationHistory As Recordset ' our many table
Dim mydb As Database

Dim IngEquipmentID As Long
Dim strProductName As String
Dim intMemoLines As Integer, intMemoLines2 As Integer,
intMemoLines3 As Integer
Dim strOneLine As String, strOneLine2 As String, strOneLine3 As
String
Dim I As Integer

On Error Resume Next

Set mydb = CurrentDb
Set rstEquipment = mydb.OpenRecordset("Equipment")
Set rstEquipmentLocationHistory =
mydb.OpenRecordset("EquipmentHistoryLocation")

rstEquipment.MoveFirst
Do Until rstEquipment.EOF

IngEquipmentID = rstEquipment!Abbreviation
strMemotxt = Nz(rstEquipment!USERLocationHistory, "")
intMemoLines = strDCount(strMemotxt, vbCrLf) + 1

For I = 1 To intMemoLines

strOneLine = ParseLoc(strMemotxt)
strOneLine2 = ParseFirstDate(strMemotxt)
strOneLine3 = ParseSecondDate(strMemotxt)

If strMemotxt <"" Then
rstEquipmentLocationHistory.AddNew
rstEquipmentLocationHistory!EquipmentNumber =
IngEquipmentID
rstEquipmentLocationHistory!Location = strOneLine
rstEquipmentLocationHistory!DateIn = strOneLine2
rstEquipmentLocationHistory!DateOut = strOneLine3
rstEquipmentLocationHistory.Update

End If
Next I
rstEquipment.MoveNext
Loop
rstEquipment.Close
Set rstEquipment = Nothing
rstEquipmentLocationHistory.Close
Set rstEquipmentLocationHistory = Nothing
End Sub

Public Function strDCount(mytext As String, delim As String) As Integer
Dim intPtr As Integer
Dim intFound As Integer
Dim delimLen As Integer

delimLen = Len(delim)

intPtr = InStr(mytext, delim)

Do While intPtr
intFound = intFound + 1
intPtr = intPtr + delimLen
intPtr = InStr(intPtr, mytext, delim)
Loop

strDCount = intFound
End Function

Function ParseLoc(pstrMemo As String) As String
ParseLoc = Left(pstrMemo, InStr(1, pstrMemo, vbTab & vbTab) - 1)
End Function

Function ParseFirstDate(pstrMemo As String) As String
Dim intDateStart As Integer, intDateStop As Integer
intDateStart = InStr(1, pstrMemo, vbTab & vbTab) + 2
intDateStop = InStr(intDateStart, pstrMemo, vbTab) - 1
ParseFirstDate = Mid(pstrMemo, intDateStart, intDateStop -
intDateStart + 1)
End Function

Function ParseSecondDate(pstrMemo As String) As String
Dim intTwoTab As Integer
Dim intDateStart As Integer
Dim intDateStop As Integer
intTwoTab = InStr(1, pstrMemo, vbTab & vbTab) + 2
intDateStart = InStr(intTwoTab, pstrMemo, vbTab & vbTab) + 2
intDateStop = InStr(intDateStart, pstrMemo, vbCr) - 1
ParseSecondDate = Mid(pstrMemo, intDateStart, intDateStop -
intDateStart + 1)
End Function
Any info would be appreciated,

Ron


Larry Linson wrote:

"RMC" <Mc*************@hotmail.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...

I though it was quitre clear, though. I stated in between the loction
and dates, they are Tabs(Tab key pressed twice) and aty the end of the
line a cariage retuern key.

Sorry, I overlooked the part about the Tabs.

The following Function procedures, if placed in a Standard Module, could be
called from within the Query you use as Record Source for your Report, or
elsewhere, if you prefer -- each stands alone, and does not rely on the
others. At the cost of reduced readability, they could be shortened somewhat
(but my preference is for readability over minimal/questionable improvement
in "efficiency").

Function ParseLoc(pstrMemo As String) As String
ParseLoc = Left(pstrMemo, InStr(1, pstrMemo, vbTab & vbTab) - 1)
End Function

Function ParseFirstDate(pstrMemo As String) As String
Dim intDateStart As Integer, intDateStop As Integer
intDateStart = InStr(1, pstrMemo, vbTab & vbTab) + 2
intDateStop = InStr(intDateStart, pstrMemo, vbTab) - 1
ParseFirstDate = Mid(pstrMemo, intDateStart, intDateStop - intDateStart +
1)
End Function

Function ParseSecondDate(pstrMemo As String) As String
Dim intTwoTab As Integer
Dim intDateStart As Integer
Dim intDateStop As Integer
intTwoTab = InStr(1, pstrMemo, vbTab & vbTab) + 2
intDateStart = InStr(intTwoTab, pstrMemo, vbTab & vbTab) + 2
intDateStop = InStr(intDateStart, pstrMemo, vbCr) - 1
ParseSecondDate = Mid(pstrMemo, intDateStart, intDateStop)
End Function

And, of course, you may wish to add some error handling.

Larry Linson
Microsoft Access MVP

[Larry Linson]

"RMC" <Mc*************@hotmail.comwrote

Thank's, I've started incorporating this into my function and i'ts
working descent. I've decided to create a table and have the memo field
data extracted with the ID. The only issue I'm having is that, it
populated the table with the right amount of record info though only
using the first line of the memo data. Other works, if one ID has4
lines within the memo field, it repeats the data in the newly created
table with the first line of memo info 4 times.

That's because you didn't move the starting point for parsing past the
previously parsed lines. I guess it was not clear to me that one Memo Field
would have multiple lines of location/date/date, so chalk that up to my
misunderstanding.

You could either very carefully manage a "start point" variable and add it
to the parsing functions, or you could save the Memo information in a
variable, and only retain everything to the right of the carriage return
after you process one line of it.

Also, would it be possible to run this within a SQl statement for a
report and not bother with a table creation?

No, I don't think so... what you have is a multivalue variable, or a "table
within a Memo Field" and that doesn't lend itself to being parsed out in the
SQL statement.

Larry Linson
Microsoft Access MVP

[RMC]

My Mistake Larry, I should have been more clear on the description. I
appreciate your assistance.

I've modified my code and it seems to work well for the ID and the
first date, usinmg the first 2 functions you sent me.
ID <tab><tab>00/00/1999<tab><tab>00/00/1999

The function:

Function ParseSecondDate(pstrMemo As String) As String
Dim intTwoTab As Integer
Dim intDateStart As Integer
Dim intDateStop As Integer
intTwoTab = InStr(1, pstrMemo, vbTab & vbTab) + 2
intDateStart = InStr(intTwoTab, pstrMemo, vbTab & vbTab) + 2
intDateStop = InStr(intDateStart, pstrMemo, vbCr) - 1
ParseSecondDate = Mid(pstrMemo, intDateStart, intDateStop -
intDateStart + 1)
End Function

trips over at the end. Seems the intDateStop variable becomes a -1.
I've verified and retyped the data in the memo fileds to make sure the
tabs are spaced properly, though still cannot figure it out.

I would never write or setup an application to use memo fields like
this, not sure why developers actually do this.

Can you help me?
Ron
Larry Linson wrote:

"RMC" <Mc*************@hotmail.comwrote

Thank's, I've started incorporating this into my function and i'ts
working descent. I've decided to create a table and have the memo field
data extracted with the ID. The only issue I'm having is that, it
populated the table with the right amount of record info though only
using the first line of the memo data. Other works, if one ID has4
lines within the memo field, it repeats the data in the newly created
table with the first line of memo info 4 times.

That's because you didn't move the starting point for parsing past the
previously parsed lines. I guess it was not clear to me that one Memo Field
would have multiple lines of location/date/date, so chalk that up to my
misunderstanding.

You could either very carefully manage a "start point" variable and add it
to the parsing functions, or you could save the Memo information in a
variable, and only retain everything to the right of the carriage return
after you process one line of it.

Also, would it be possible to run this within a SQl statement for a
report and not bother with a table creation?

No, I don't think so... what you have is a multivalue variable, or a "table
within a Memo Field" and that doesn't lend itself to being parsed out in the
SQL statement.

Larry Linson
Microsoft Access MVP