Extract certain data from a memo field 1

[ruthcali]

I have a table called Remarks with a field called Remarks_text (data type--memo) which i get from an external source.

Remarks_text is a log. Whenever someone writes a remark, the name and time gets stamped automatically.

I also have a table called Employees.

I would like to have a form or report that extracts the remarks_text if they are written by someone in the Employees table.

For example, this is one entry in the remarks_text:

Mar 26 2003 4:13PM - John Victoro
The following project has been received in the office on 03/22/03.

Mar 26 2003 3:40PM - Kerry Akeve
Specs was shipped today (03/26)

Mar 26 2003 8:54AM - Peter Bruce
System is connected and under test. We are running errors on the project. Await arrival of other equipment.

Mar 26 2003 8:19AM - Kerry Akeve
Revision 2 released


In the Employees table, i have John Victoro and Kerry Akeve. So for this record, i would like the form or report to only show:

Mar 26 2003 4:13PM - John Victoro
The following project has been received in the office on 03/22/03.

Mar 26 2003 3:40PM - Kerry Akeve
Specs was shipped today (03/26)

Mar 26 2003 8:19AM - Kerry Akeve
Revision 2 released

Does anyone have any ideas how to do this?
Thanks,
ruth

 

[Trendsetter]

Make a query from both tables and join them via Employee_Name and set the join to only show records that exist records in both tables

 

[ruthcali]

Trendsetter,
Thanks for writing.

The Remarks table doesn't have an employee_name field. The employee names are all inside the remarks_text memo field.

The example above is all in one field.
In other words, the following is all just one single entry in one remarks_text memo field:

Mar 26 2003 4:13PM - John Victoro
The following project has been received in the office on 03/22/03.

Mar 26 2003 3:40PM - Kerry Akeve
Specs was shipped today (03/26)

Mar 26 2003 8:54AM - Peter Bruce
System is connected and under test. We are running errors on the project. Await arrival of other equipment.

Mar 26 2003 8:19AM - Kerry Akeve
Revision 2 released


So i need to weed out the entries that don't have a matching name in the Employees table.

 

[Trendsetter]

The most efficient way of doing what you want is to introduce 2 new fields to the remarks table and have these two fields as "required" fields on the Remarks Input form.
The first field should be Date & Time which can have a default value of Now() which is today's date and the time of day.
The second The Employee should be a combo and be populated from the Employees table but allow enter other than from the list.
this way you can be sure that you can connect your data in a regular manner and be more certain than you have some semblance of control.

 

[Steve101]

Ruth,

You do not make it clear as to how many records there are in the Remarks table. Am I correct in assuming that it is just a single record, with entries concatenated as required.

At any rate it would seem to be evident that you will have to parse the Remarks memo field programatically, extracting each "entry" into its date, person and comment components, then check the person against the Employee table, and if there's a match, output the entry (or save it to a new table, whatever).

The point here is that because of the lack of structure of the remarks table (both from a row and column point of view), you wont resolve this through simple queries. It will needs some VBA parsing code to do the initial cutting up of the data.

The parsing is not a trivial problem; you will have to recognise where the various fields start and end, but providing this is "well behaved", this should'nt be too hard (eg. the Date always has a blank line in front of it and has Jan, Feb, .. Dec as the first three characters. You'd have to 'condition' the field to make this true for the first entry. The name always follows the data and is 15 (or whatever) characters from the start of the line.

... and so on.

Hope this helps; since you're a programmer, this should hopefully get you started,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[ruthcali]

Thanks for writing!

Trendsetter,
I import the Remarks table via ODBC and have no control over its structure. So i can't add new fields to it.

Steve,
The remarks table has 2 fields:
proj_num (text) and remarks_text (memo).
The table currently has 3800 records.

For example, here is ONE record from my table:
--proj_num = 279
--remarks_text =
Mar 26 2003 4:13PM - John Victoro
The following project has been received in the office on 03/22/03.

Mar 26 2003 3:40PM - Kerry Akeve
Specs was shipped today (03/26)

Mar 26 2003 8:54AM - Peter Bruce
System is connected and under test. We are running errors on the project. Await arrival of other equipment.

Mar 26 2003 8:19AM - Kerry Akeve
Revision 2 released.

All that above is ONE big record in the remarks_text field for the proj_num 279. (In the table, there are little squares to signify line breaks in the remarks_text memo field).

You're right about the parsing and comparing to the Employees table. Unfortunately, that is where i am stuck.

i have been trying to figure out this code for 3 days. i would say i am a beginner programmer and this code is very advanced.

 

[Rick39]

I do something similar to what you're trying to get to with memo fields by finding a delimeter ($ in my case - you could use the line break/return) and parsing the text into individual records in another table....this could parse your memo text by proj_num and individually numbered notes that could easily be queried for reports

You'll need a table where (referencing the current code):

TODCR - proj_num
Task/Procedure ID - you wouldn't need
NOTEID - you wouldn't need
NOTE - your parsed note would go here

Public Sub ParseAirframeRecChgs()
Dim strAString As String, sql As String
Dim i As Integer, x As Integer
Dim intCnt As Integer
Dim db As Database
Dim rs, rs1 As Recordset


sql = "SELECT TODCR.[Control Number], TODCR.[Your Source Memo Field], TODCR.[Task/Procedure ID], TODCR.Type"
sql = sql & " FROM TODCR"
sql = sql & " WHERE (((TODCR.[Your Source Memo Field]) is not null));"



Set db = CurrentDb
Set rs = db.OpenRecordset(sql, DB_OPEN_DYNASET)
Set rs1 = db.OpenRecordset("AIRFRAME_TODCR_REC_CHG", DB_OPEN_DYNASET)
rs.MoveLast
rs.MoveFirst



For x = 1 To rs.RecordCount
'Find out how many blocks between $ signs
strAString = rs![Your Source Memo Field]
intCnt = CountCSWords(strAString)


'Now call the other function to retrieve each one in turn and link with ControlNumber
For i = 1 To intCnt
rs1.AddNew
rs1!NOTE = GetCSWord(strAString, i)
rs1!TODCR = rs![Control Number]
'rs1!NOTEID = rs![Control Number] & "-" & Format(CStr(i) - 1, "0#&quot
'rs1![Task/Procedure ID] = rs![Task/Procedure ID]
rs1.Update
Next
rs.MoveNext

Next x
rs.Close
rs1.Close

End Sub

Function CountCSWords(ByVal s) As Integer
'Counts the words in a string that are separated by commas.
' You could substitute the line break or return for the $ here
Dim WC As Integer, Pos As Integer
If VarType(s) <> 8 Or Len(s) = 0 Then
CountCSWords = 0
Exit Function
End If
WC = 1
Pos = InStr(s, &quot;$&quot
Do While Pos > 0
WC = WC + 1
Pos = InStr(Pos + 1, s, &quot;$&quot
Loop
CountCSWords = WC
End Function

Function GetCSWord(ByVal s, Indx As Integer)
'Returns the nth word in a specific field.
Dim WC As Integer, Count As Integer
Dim SPos As Integer, EPos As Integer

WC = CountCSWords(s)
If Indx < 1 Or Indx > WC Then
GetCSWord = Null
Exit Function
End If
Count = 1
SPos = 1
For Count = 2 To Indx
SPos = InStr(SPos, s, &quot;$&quot + 1
Next Count
EPos = InStr(SPos, s, &quot;$&quot - 1
If EPos <= 0 Then EPos = Len(s)
GetCSWord = Trim(Mid(s, SPos, EPos - SPos + 1))
End Function

 

[ruthcali]

Wow Rick, Thanks!! that's impressive. i'm working on trying to fit it to my needs.

I'll keep you posted...

 

[ruthcali]

Rick,

Thanks again so much for your help above. But, I've been trying for a few days, but i'm having trouble incorporating your code into what i need.

Does anyone have any help for me?

Thanks,

 

[judgehopkins]

Remarks_text is a log. Whenever someone writes a remark, the name and time gets stamped automatically.

First of all, how do you that (above)?

Second, if I understand the problem correctly, you are looking through a text field for a certain string (i.e., an employee's name).

IF that is correct, then I think you have to step back and look at the logic. Your users are not going to enter names correctly all the time.

IF that is NOT correct, then are you getting the employee name from a table?

Thanks!

 

[Rick39]

Can you describe where you're stuck?

 

[ruthcali]

Judge,

The raw data is entered via a website on our company's intranet. To get to the website, you have to log in with a name and password. Then, if you want to enter a remark for a project, you navigate to the correct project_id record, click on the Remarks tab and hit 'edit'.

The time/date/employee stamp is automatically entered as part of that website. (i didn't create the website). Therefore, the names will always be entered consistently.

I get the raw data into Access by importing a table called Remarks using ODBC. The Remarks table has 2 fields: proj_num and remarks_text. One record of the remarks_text field for one proj_id looks like:

Mar 26 2003 4:13PM - John Victoro [square]The following project has been received in the office on 03/22/03.[square]
Mar 26 2003 3:40PM - Kerry Akeve [square]Specs was shipped today (03/26)Mar 26 2003 8:54AM - Peter Bruce[square]System is connected and under test. We are running errors on the project. Await arrival of other equipment.[square]Mar 26 2003 8:19AM - Kerry Akeve[square]Revision 2 released

The word [square] is actually a little square which signifies a link break.

I have a table in my database called Employees and i have the first and last names of 10 of our employees.

Your comment is correct: &quot;Second, if I understand the problem correctly, you are looking through a text field for a certain string (i.e., an employee's name).&quot; Except i am looking thru a Memo field, not a text field.

I want a report that weeds out all entries not written by one of our 10 employees.

Rick,
I don't think i want to create a new table since my Remarks table is very big and that new table would be very big.

I think i need a recordset within a recordset. One recordset would open the Remarks table and one would open the Employees table. Then if it found a match, it would take the data between the 2 dates and save it in a temporary field. then it would keep going until it reached the end of the 2 recordsets. it would do this for all the Open projects and display the data on a report. Does that sound right?

Thanks!