UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Memo Field Partial Parsing, Access 2010    
 
   
Stargrove
post Jan 6 2017, 04:05 PM
Post#1



Posts: 4
Joined: 6-January 17



Today we generate a weekly activity report by hand in Excel using information that has already been entered in our main Access work management database. On our work order form we have a memo field that we enter current status. Each entry is preceded with its date of entry and each entry is separated by one or more CRLF (using ALT-ENT) with the newest entry at the top of the field displayed on the form. So we don't have to enter information in two different places we would like to spit out a report or, more likely, the spreadsheet format we use today with the last few (probably 3 to 5) entries included that we can use in our report.

I have looked around here a bit and it seems most folks are trying to move large amounts of data from memo fields into individual table records and the processes for that do not seem to really fit what we want to do.

Thanks in advance,
James

PS (bonus question): We currently put change bars in our spreadsheet to make it quick for management to see what statuses have changed in the last week. We do track the last data a work order record was last updated in our database today. Is there a way to do something in a report that would indicate something was updated in the last 7-days?
Go to the top of the page
 
doctor9
post Jan 6 2017, 04:36 PM
Post#2


UtterAccess Editor
Posts: 16,845
Joined: 29-March 05
From: Wisconsin


James,

I'm not really into answering more than one question at once; it causes too much confusion, so I'll just focus on the parsing issue. Assuming you don't have millions of records, we can probably write you a subroutine that finds each dated status, and writes it to a record in the appropriate table of statuses as long as they follow the "carriage return/line feed/some sort of date" format for the delimiter.

Are you hoping to do this once to fix existing data, and then start working with the statuses as separate inputs from now on? Or is this something that needs to be handled for old data and new data as well, as an ongoing issue? Do you want to remove the statuses from the memo field after successful parsing? Is there ANYTHING else in the memo field besides the varying number of statuses?

Dennis

EDIT: So would this be a good example of how a typical memo field value would look for one record?
CODE
1/1/17 - Status 1.  Bought a coffee maker.
1/2/17 - Status 2.  Filled the coffee maker.
It started bubbling shortly after that.
But no coffee.

1/3/17 - Status 3.  Cleaned coffee maker.
1/4/17 - Status 4.  Filled the coffee maker again.  Bubbled again.
This time it made coffee.
And there was much rejoicing.

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Stargrove
post Jan 6 2017, 06:58 PM
Post#3



Posts: 4
Joined: 6-January 17



Let me try again...

We have a current project status Memo field in a work order database that we want to continue to use. It has data formatted as follows:
(2017-01-06) Received requirements document from customer and have started engineering system design to get them a cost estimate.

(2016-12-18) Met with customer on how filling out new requirements document. Customer has until 1/5/2017 to get that done and will email it back.

(2016-12-15) Talked to customer about how to move forward on project.

(2016-12-01) Received initial email from customer stating some requirements. Email back stating we need to put together a requirements doc so we can get an estimate together for them.

All of our work orders have this field and some of them have data going back a few years with possible many dozen or more entries. Some of the entries in a given field may have the date formatted as follows "2016-12-18: ..." but they do all have a date and new entries all start on a new line and most have a line in between them to make them easier to read.

We are currently producing a weekly spreadsheet (just a copy of the previous week with closed ones removed and possibly new ones added) with status information that has the same status information that is also in the work order memo field. We want to be able to click a button and collect the most recent 3 to 5 entries in that memo field (along with a few other standard text fields) and produce a report in either Access or get the data out to Excel for further formatting. At any given time we have around 75 to 100 open work orders but we will probably set up some way to pick out ones we want in our report since we don't report status on all of them every week.

I hope this clarifies things.

James
Go to the top of the page
 
doctor9
post Jan 9 2017, 03:15 PM
Post#4


UtterAccess Editor
Posts: 16,845
Joined: 29-March 05
From: Wisconsin


James,

I'm afraid this is more confusing. Can you clarify all of the differences between your new example and your previous description? For example:

> each entry is separated by one or more CRLF
Your example seems to have no instances of more than one CRLF. Also, your dates are embedded within parentheses, which you didn't mention. Your new description seems to say that the parentheses go away for "some of the entries". Is this accurate?

> We want to be able to click a button and collect the most recent 3 to 5 entries in that memo field (along with a few other standard text fields)

This wasn't mentioned in the original post. Why is the number of entries that you want to collect not fixed? How do you determine when to get 3, 4 or 5 entries?

It might help to provide a more specific example that shows all of the variances of the data in a single entry, as well as what you want to extract and how you determine what to extract and not extract.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Stargrove
post Jan 9 2017, 07:13 PM
Post#5



Posts: 4
Joined: 6-January 17



QUOTE
> each entry is separated by one or more CRLF
Your example seems to have no instances of more than one CRLF. Also, your dates are embedded within parentheses, which you didn't mention. Your new description seems to say that the parentheses go away for "some of the entries". Is this accurate?


Yes, we have sort of a standard that we try to follow where we enter new lines using CTRL-ENT (is that and enclose the date in parens. We also try to include another CTRL-ENT between entries to make the info more readable.

QUOTE
> We want to be able to click a button and collect the most recent 3 to 5 entries in that memo field (along with a few other standard text fields)

This wasn't mentioned in the original post. Why is the number of entries that you want to collect not fixed? How do you determine when to get 3, 4 or 5 entries?


I assume we will be able to set this in the module/code depending on what we decide is appropriate.
------------------------

I have attached a sample with a few fields that we would want with the data perfectly formatted. If we do this we will have to make sure our 6 users are aware of how the data will need to be formatted so it shows up properly in the report. Since we would only be interested in the last 3-5 entries then we will not have to worry about data that is improperly formatted before that threshold.

As far as what we would extract...we will take the entry as-is with the date and text as written since that is how we enter the info into Excel today. The only wrinkle in that is we usually do not put the extra CTRL-ENT in between the Excel entries due to cell row height limitations (409) in Excel and may have to pull out the extra line feed that we put in the database.

Hope this helps,

James
Attached File(s)
Attached File  WO_Sample.zip ( 21.58K )Number of downloads: 1
 
Go to the top of the page
 
ADezii
post Jan 9 2017, 08:38 PM
Post#6



Posts: 1,513
Joined: 4-February 07
From: USA, Florida, Delray Beach


Like doctor9 I am also confused. but I am attaching nothing more than an educated guess on my part.
This post has been edited by ADezii: Jan 9 2017, 08:40 PM
Attached File(s)
Attached File  WO_Sample_Revised.zip ( 49.64K )Number of downloads: 1
 
Go to the top of the page
 
Stargrove
post Jan 9 2017, 10:27 PM
Post#7



Posts: 4
Joined: 6-January 17



Well, with a little tweak to the module you included in the last version I think I have a good start to what I want. I have attached the result of what I did here.

CODE
Public Function fParseWO(strStatus As String) As String
Dim varStatus As Variant
Dim intCtr As Integer
Dim strBuild As String

varStatus = Split(strStatus, vbCrLf & vbCrLf)

'For intCtr = LBound(varStatus) To UBound(varStatus)
For intCtr = 0 To 2
  strBuild = strBuild & varStatus(intCtr) & vbCrLf
Next

fParseWO = Left$(strBuild, Len(strBuild) - 2)
End Function


The change I made to your module gave me the last three entries from the memo field. If I changed it to "For intCtr = 0 to 4" I would get the last five.

Thanks,
James
Attached File(s)
Attached File  WO_Sample_Revised2.zip ( 55.92K )Number of downloads: 1
 
Go to the top of the page
 
ADezii
post Jan 10 2017, 08:42 AM
Post#8



Posts: 1,513
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif
You could also try a simpler approach, namely replacing a Double CrLf with a Single, as in:
CODE
New_String = Replace(<Status String>, vbCrLf & vbCrLf, vbCrLf)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th March 2017 - 11:42 PM