parsing out dates from text

I have a customer tracking program (ADP), and it was originally designed 
with teh customer notes as a "text" field.  Then then customer notes woudl 
be entered at various times, it would be entered by automatically adding a 
lineedn cahracter, followed by the date, followed by the user name in 
parentheses.  However, the customer notes are not very useful for searching 
or for filtering for various types of comments. A sample of a customer notes 
data woudl look like this:

10/26/05 (Stephen): Fax Submitted to Amy regarding Shipment Approval
11/2/05 (julie):approved extension
1/24/06 (jchi): Faxed Submitted to Amy regarding COD Approval.
3/21/06 (anna): Fax Submitted to Amy regarding COD Approval
5/11/06 (Stephen): Fax Submitted to Amy regarding NSF
7/13/06 (jchi): Fax Submitted to Amy Smith regarding COD Approval
8/21/06 (jchi): Fax Submitted to Amy Smith regarding COD Approval
9/20/06 (jchi): Fax Submitted to Amy Smith regarding COD Approval
11/30/06 (Stephen): Received fax regarding ownership change. Changes made to 
E. J Smith in the system
1/17/07 (jchi): Fax Submitted to Amy Smith regarding COD Approval
2/21/07 (jchi): Fax Submitted to Amy Smith regarding COD Approval
5/3/07 (jchi): Fax Submitted to Amy Smith regarding COD Approval

I am looking to change teh design of the customer notes section so that it 
has a separate record for each entry.  Effectively, each date would have 
aseparate record.  I am looking to be able to have a query or some VB code 
that can me parse through the text of the customer notes and then create 
separate those records.

Is there a way to write some code or use SQL to look at the text field and 
find a date within the text, then find the next date, so it might do the 
following:

Find DATE1, and FIND DATE2
Put DATE1 into FIELD1(i.e.note date) of new record
find name in parenthese between DATE1 and DATE2 and copy that into FIELD2 
(i.e. username)
Put all other text up to (but not including DATE2) into FIELD3 (i.e. Notes) 
of new record

If this possible? I guess I can write some VB script myself, but I don't 
know how to tell the code to LOOK for a date string within the larger text 
string.

help?


0
Steve
5/29/2007 4:44:45 AM
access.queries 6343 articles. 1 followers. Follow

3 Replies
806 Views

Similar Articles

[PageSpeed] 27

I think the best thing would be to use the left function and grab the first 
8 characters.

expr1: left([notes],8)

then I think you may be able to put a date format function around that.

-- 
http://www.patriotsquestion911.com
http://www.911truth.org
http://www.wtc7.net
http://www.st911.org
http://www.journalof911studies.com
http://www.pilotsfor911truth.org
http://ae911truth.org




"Steve" wrote:

> I have a customer tracking program (ADP), and it was originally designed 
> with teh customer notes as a "text" field.  Then then customer notes woudl 
> be entered at various times, it would be entered by automatically adding a 
> lineedn cahracter, followed by the date, followed by the user name in 
> parentheses.  However, the customer notes are not very useful for searching 
> or for filtering for various types of comments. A sample of a customer notes 
> data woudl look like this:
> 
> 10/26/05 (Stephen): Fax Submitted to Amy regarding Shipment Approval
> 11/2/05 (julie):approved extension
> 1/24/06 (jchi): Faxed Submitted to Amy regarding COD Approval.
> 3/21/06 (anna): Fax Submitted to Amy regarding COD Approval
> 5/11/06 (Stephen): Fax Submitted to Amy regarding NSF
> 7/13/06 (jchi): Fax Submitted to Amy Smith regarding COD Approval
> 8/21/06 (jchi): Fax Submitted to Amy Smith regarding COD Approval
> 9/20/06 (jchi): Fax Submitted to Amy Smith regarding COD Approval
> 11/30/06 (Stephen): Received fax regarding ownership change. Changes made to 
> E. J Smith in the system
> 1/17/07 (jchi): Fax Submitted to Amy Smith regarding COD Approval
> 2/21/07 (jchi): Fax Submitted to Amy Smith regarding COD Approval
> 5/3/07 (jchi): Fax Submitted to Amy Smith regarding COD Approval
> 
> I am looking to change teh design of the customer notes section so that it 
> has a separate record for each entry.  Effectively, each date would have 
> aseparate record.  I am looking to be able to have a query or some VB code 
> that can me parse through the text of the customer notes and then create 
> separate those records.
> 
> Is there a way to write some code or use SQL to look at the text field and 
> find a date within the text, then find the next date, so it might do the 
> following:
> 
> Find DATE1, and FIND DATE2
> Put DATE1 into FIELD1(i.e.note date) of new record
> find name in parenthese between DATE1 and DATE2 and copy that into FIELD2 
> (i.e. username)
> Put all other text up to (but not including DATE2) into FIELD3 (i.e. Notes) 
> of new record
> 
> If this possible? I guess I can write some VB script myself, but I don't 
> know how to tell the code to LOOK for a date string within the larger text 
> string.
> 
> help?
> 
> 
> 
0
Utf
5/29/2007 8:27:01 AM
On Mon, 28 May 2007 21:44:45 -0700, "Steve" <webmail@thesecongroup.com> wrote:

>10/26/05 (Stephen): Fax Submitted to Amy regarding Shipment Approval

>I am looking to change teh design of the customer notes section so that it 
>has a separate record for each entry.  Effectively, each date would have 
>aseparate record.  I am looking to be able to have a query or some VB code 
>that can me parse through the text of the customer notes and then create 
>separate those records.
>
>Is there a way to write some code or use SQL to look at the text field and 
>find a date within the text, then find the next date, so it might do the 
>following:
>
>Find DATE1, and FIND DATE2
>Put DATE1 into FIELD1(i.e.note date) of new record
>find name in parenthese between DATE1 and DATE2 and copy that into FIELD2 
>(i.e. username)
>Put all other text up to (but not including DATE2) into FIELD3 (i.e. Notes) 
>of new record
>
>If this possible? I guess I can write some VB script myself, but I don't 
>know how to tell the code to LOOK for a date string within the larger text 
>string.

I'm more familiar with VBA than VBScript so there may be some differences
here, but I expect they'll use the same functions. The problem will be with
records which DON'T fit the paradigm (and you can bet your bottom dollar there
will be such records!!!). Here's some sketchy air code:

Dim strMemo As String
Dim strLine As String
Dim strDate As String
<open a recordset rs on your table>
<load the memo field into strMemo>
Do Until strMemo = ""
' extract the first line
strLine = Left(strMemo, InStr(strMemo & vbCrLf, vbCrLf) - 1)
' trim off the first line
strMemo = Mid(strMemo, InStr(strMemo & vbCrLf, vbCrLf) + 2)
strDate = Left(strLine, InStr(strLine, " ") - 1)
If IsDate(strDate) Then
   rs!Date1 = CDate(strDate)
Else
   <handle the erroneous data>
End If
rs!Field2 = Mid(strLine, InStr(strLine, "(") + 1), InStr(strLine, ")") - 1)
rs!Notes = Trim(Mid(strLine, InStr(strLine, ")") + 1)


             John W. Vinson [MVP]

0
John
5/29/2007 3:57:00 PM
John -

This was excellent!  It gave me 80% of the background code.  I then had to 
write some custom exception code to handle all of the weird syntax that was 
non standard.  In the end, it was GREAT

Thank you.


"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:5nio53tehaarrgusbie8taji631amr3t0g@4ax.com...
> On Mon, 28 May 2007 21:44:45 -0700, "Steve" <webmail@thesecongroup.com> 
> wrote:
>
>>10/26/05 (Stephen): Fax Submitted to Amy regarding Shipment Approval
>
>>I am looking to change teh design of the customer notes section so that it
>>has a separate record for each entry.  Effectively, each date would have
>>aseparate record.  I am looking to be able to have a query or some VB code
>>that can me parse through the text of the customer notes and then create
>>separate those records.
>>
>>Is there a way to write some code or use SQL to look at the text field and
>>find a date within the text, then find the next date, so it might do the
>>following:
>>
>>Find DATE1, and FIND DATE2
>>Put DATE1 into FIELD1(i.e.note date) of new record
>>find name in parenthese between DATE1 and DATE2 and copy that into FIELD2
>>(i.e. username)
>>Put all other text up to (but not including DATE2) into FIELD3 (i.e. 
>>Notes)
>>of new record
>>
>>If this possible? I guess I can write some VB script myself, but I don't
>>know how to tell the code to LOOK for a date string within the larger text
>>string.
>
> I'm more familiar with VBA than VBScript so there may be some differences
> here, but I expect they'll use the same functions. The problem will be 
> with
> records which DON'T fit the paradigm (and you can bet your bottom dollar 
> there
> will be such records!!!). Here's some sketchy air code:
>
> Dim strMemo As String
> Dim strLine As String
> Dim strDate As String
> <open a recordset rs on your table>
> <load the memo field into strMemo>
> Do Until strMemo = ""
> ' extract the first line
> strLine = Left(strMemo, InStr(strMemo & vbCrLf, vbCrLf) - 1)
> ' trim off the first line
> strMemo = Mid(strMemo, InStr(strMemo & vbCrLf, vbCrLf) + 2)
> strDate = Left(strLine, InStr(strLine, " ") - 1)
> If IsDate(strDate) Then
>   rs!Date1 = CDate(strDate)
> Else
>   <handle the erroneous data>
> End If
> rs!Field2 = Mid(strLine, InStr(strLine, "(") + 1), InStr(strLine, ")") - 
> 1)
> rs!Notes = Trim(Mid(strLine, InStr(strLine, ")") + 1)
>
>
>             John W. Vinson [MVP]
> 


0
Steve
5/31/2007 7:57:15 AM
Reply:

Similar Artilces:

Text box in chart disappears but reappears when in spreadsheet
Has anyone any idea how to prevent a text box that's in a graph from disappearing when the graph is selected, but reappearing when cursor is back in the spreadsheet? This never used to happen before, but only recently has begun occuring with our graphs. Thanks! Hi, That behavior would suggest the textbox is not embedded in the chart but just placed over it. Can you move the textbox away from the chart? If yes then delete the textbox and then select the chart and paste. Cheers Andy Eggcel wrote: > Has anyone any idea how to prevent a text box that's in a graph from > di...

Parsing Question
I've done parsing where you have one delimiter such as a comma or space. What if you have more than one? Example data: sStr(0) = "3/9" sStr(1) = "3/11 3/15-" sStr(2) = "3/8+ 3/12" sStr(3) = "3/9 3/15" sStr(4) = "3/8- 3/10+ 3/15" The strings above are Month/Day with some having an additional - or + character next to it. These dates are extracted from Excel cells as strings. I need to break those strings down into individual dates. So for sStr(4), I need to parse out "3/8", "3/10" and "3/15&...

Date column defaults
Outlook's default date column is 'Best Fit'. I'm not sure quite what this means, but I prefer the more compact dd/mm/yyyy (UK). Is there any way to change this globally or do I have to laboriously change each instance? Can I change the default for new columns? -- Jonathan Finney Jonathan Finney <plegron@hotmail.com> wrote: > Outlook's default date column is 'Best Fit'. I'm not sure quite what > this means, but I prefer the more compact dd/mm/yyyy (UK). It means that Outlook decides the width of the field. The date format display in the f...

Text size #4
I'm sure this is probably very easy! but I can't find any way to increase the size of an email in the viewing panel of Outlook, is there a way to do this and possibly to add a button to the toolbar to enable easy resizing of text? Thanks "Harry Limey" <harrylimey(at)Lycos.co.uk> wrote in message news:%23m1mP7xBIHA.1188@TK2MSFTNGP04.phx.gbl... > I'm sure this is probably very easy! but I can't find any way to increase > the size of an email in the viewing panel of Outlook, is there a way to do > this and possibly to add a button to the toolbar...

Text list to graph
Hi: I have a column listing car makes in random order with duplicates: Mazda Honda Honda Toyota Chevy Mazda... etc. QUESTION: How can I create a chart from this list? I've tried sorting, then sub-totaling. Am I on the right track? Thanks, Mark Flynn You can summarize the data with a Pivot Table, and create a chart from that. Add a heading to the list, e.g. Cars Select a cell in the table. Choose Data>PivotTable and PivotChart Report Select PivotChart report (with PivotTable report), click Next Select the Data range, click Next Click Layout Drag the Cars button to the Row area Dr...

Date Stamp on CRM Email Templates
Client would like to be able to have the ability to add date and time to the email template. Is this template function (data) avaliable to select from in CRM 3 or 4. Using the server time for example. ...

how to parse <?xml version="1.0" encoding="UTF-8"?> with xpath? is it possible?
how to parse <?xml version="1.0" encoding="UTF-8"?> with xpath? is it possible? "Daniel" <softwareengineer98037@yahoo.com> wrote in message news:%23g11DbDOGHA.2176@TK2MSFTNGP10.phx.gbl... > how to parse <?xml version="1.0" encoding="UTF-8"?> with xpath? is it > possible? No -- the xml declaration is not represented in the XML Infoset. Cheers, Dimitre Novatchev ...

Time and date on an invoice.
How can you tell what user edited the invoice and what time they did so. I am on 8.0gp thanks josh Josh, The audit trails in V8 were weak and your options with the base code are limited. There are fields in some tables called something like CreatedID or CreatedUserID (but very much abbreviated) along with creation dates. Some tables have last modified user and date/time. Look at the tables you are working with in Resource Descriptions to see if they have these fields. You will need to access them through a report writer or Smart List Builder. There are some 3rd party tools availab...

Date conversions....Excel thinking too hard?
We have many programs (Access, SAP, etc.), that when they try to export to Excel, Excel always tries to convert our part numbers into dates. For instance, we have a part number, 1944-1-5, that needs to be exported to Excel as a text value, 1944-1-5, not converted to a date, like 1/5/1944. And no, putting quotes around it is not practical, since we have thousands of part numbers. I have successfully exported from Access using the TransferSpreadsheet method in a macro or VBA (instead of the standard "File | Export..." menu selection) without the date conversion occuring. Ho...

Conditional formula with text and number in cell
Hello, Can a conditional formula be written to convert a cell with text and a number to a cellw ith just a number? I have a cell with the number 10 and the word "weak" in it. 10 weak Can a conditional formula convert this to just the number "10"? I did =If(b1= 10 weak,"10") but it does not work. Thank you. =3DIF(B1=3D"10 weak",10,"not 10 weak") BTW............10 weak what's? 10 weak kittens? 10 weak cups of tea? Gord Dibben MS Excel MVP On Sat, 29 May 2010 11:47:01 -0700, RichM = <RichM@discussions...

Great Plains SOP Table Date Fields
I looking for clarification on what DOCDATE and ORDRDATE are in the SOP tables. I believe that DOCDATE is the date the line was entered and that the ORDRDATE is the date that the date that the Order goes into effect. Could anyone clarify this for me? Go to the SDK and look at SOPTRX.RTF. It is the Sales Order Transaction flow by table and field. "Chris Hornung" wrote: > I looking for clarification on what DOCDATE and ORDRDATE are in the SOP > tables. I believe that DOCDATE is the date the line was entered and that the > ORDRDATE is the date that the date that the...

Appointment End Date
As soon as I enter a Start time in Outlook 2007's appointment calendar, the End time date jumps to the next day. How can I make it stay on the Start date? Thanks. "sumacny" <sumacny@discussions.microsoft.com> wrote in message news:075719D0-B754-4E0F-995C-FD02294EA888@microsoft.com... > As soon as I enter a Start time in Outlook 2007's appointment calendar, the > End time date jumps to the next day. How can I make it stay on the Start > date? Thanks. If you are double-clicking on a date in the Month view, then you are creating an all-day event ...

Dates #3
when i pivot information into a chart it changes date formation from MAR-2003 to 18/03/2003. how can i change this back. Thanks Andy - Double click on the dates, click on the Number tab, and select the desired number format. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Andy wrote: > when i pivot information into a chart it changes date > formation from MAR-2003 to 18/03/2003. how can i change > this back. > > Thanks ...

Graph displaying dates alphabetically on x axis, not by date
Hello! Title says it all - I want the x axis to show dates in DATE ORDER, not alphabetical order. I've read thru the forum, can't see how to fix it - your help will be appreciated. Kind Regards, Russell. p.s. Here is the graph Row Source, should there be an ORDER BY statement in here? SELECT (Format([Date Paid],"mmm"" '""yy")) AS Expr1, Sum([Tenant Payments]. Amount) AS SumOfAmount FROM [Tenant Payments] GROUP BY (Format([Date Paid], "mmm"" '""yy")), [Tenant Payments].[Date Paid], (Year([Date Paid]...

Dates
I am working on a spreadsheet that consists of a rolling 12 mont period. Once a month has passed how do I delete it and add a new mont to the 12 month period? Please help -- ~~ Message posted from http://www.ExcelForum.com You have to provide more info on how your data is laid out. Tell us what you have and tell us what you want to have when the month changes. HTH Otto "erodri02" <erodri02.y6nsy@excelforum-nospam.com> wrote in message news:erodri02.y6nsy@excelforum-nospam.com... > > I am working on a spreadsheet that consists of a rolling 12 month > period. Once...

Date filled down?
I would like to put a date in a cell. Then fill it down. How to do that? Like.... Tuesday, July 06, 2004 Wednesday, July 07, 2004 ..................... Hi Ming type the date as dd/mm/yy or mm/dd/yy or whatever is default for your region, then choose format / cells click on custom, type in the box on the right had side dddd, mmmm dd, yyyy click ok now click on fill handle (bottom right of cell) and fill down as far as you want - adjust column width if needed. Cheers JulieD "Ming He" <hepub@hotmail.com> wrote in message news:e$4TSb9YEHA.3304@TK2MSFTNGP09....

Date Prompts in Subreport
Is it possible to combine 2 reports together if they are both based on queries that require date prompts? The reason I am asking is that I have 2 reports of employee time, each for a different department. Both are based on queries that have the criteria Between [Beginning Date:] and [Ending Date:]. That way, when each report is generated, the user can select the date range to display. Now, I am trying to combine them so that they both show up on the same report. I tried making one a subreport of another, but when I try to run the report, I keep getting the date prompts over and ove...

How I can specify the length of a text or binary column to larger value?
When the using Microsoft Excel How I can specify the length of a text or binary column to a larger value? otherwise, if the length of a text or binary column not specified (or is specified as 0), the column length will be set to 255. Hi not really sure what you're trying to achieve? -- Regards Frank Kabel Frankfurt, Germany "Yue Wu" <wuyue15@hotmail.com> schrieb im Newsbeitrag news:433001c4a48d$76b0aa90$a601280a@phx.gbl... > When the using Microsoft Excel How I can specify the > length of a text or binary column to a larger value? > otherwise, if the lengt...

Date time picker control cannot set time from CTime mapping
Hi I have a dialog with a Date Time Picker control. Using Class-wizard i have mapped this to a CTime data member in my dialog derived class. Im trying to set this member with a date value on initialisation but it defaults to a 1970 date. The dialog class has the following member CTime mExpireDate; In the DoDataExchange there is DDX_DateTimeCtrl(pDX, IDC_DATETIMEPICKER3, mExpireDate); BOOL CActionReceivePage::OnInitDialog() { mExpireDate = rec->GetExpireDate().GetDate(); UpdateData(FALSE); } Where GetDate() returns a valid time_t value. retreiving the set value from th...

Parsing cells
Hi Folks, I have data in cells which is OCR`d from my phone bill, and some of the info is corrupt. Where I should be getting eg. 17 Nov 14:49 for date/time I am getting O17 Nov 14:49 or C17Nov 14:49. The reason is that there are unreadable icon characters to signify day or night. Questions: 1)How do I remove the faulty characters......they are always the first digit in the date if they are there. 2)How can I seperate the date and time into two adjasent colums of cells rather than their current format in one cell. Any and all help greatly appreciated. John Hi John You ca...

calculating dates #2
What formula do i use??? here is my problem... i have 3 columns A- Qty of days eg: 6, B-start date, eg: 10/12/07 C -end date, , (formula i am using, (colummn (C) =A+B+1 (copied down the column) this gives me an end date by adding A and B which i use in a gantt chart, eg; "but" some dates do not have a start date yet and the end result will read (C) 05/01/00, how can i get column (C) to remain blank until i put a start date in???? =IF(A1="","",A1+B1+1) -- Kind regards, Niek Otten Microsoft MVP - Excel "spudpeeps" <spudpeeps@discussions.mic...

email format is changed from HTML to Text
I haven't done any research on this, I hope someone has the answer. Sometimes when I reply to a message, the email format is changed from HTML to Plain text automatically, It does't happen when I am writing a new email, it is just sometimes when I am replying. Are you sure that the message to which you are replying is HTML and not just plain text with foreign encoding? For example, I set the encoding for this message to Turkish, but it is still plain text. It just doesn't appear that way (to me anyway, and possibly also you). Ray at work "Alex Perez" <aperezny@h...

Address Book Not Working With Text Messages Sent By Outlook
Every time I try to send a text message per Outlook, I can type and send the message, only as long as I physically type a person's name in the To box. If I click on the little address book button beside the To box or click on the icon at the top of the message page, I immediately get kicked out of Outlook, and it must restart. Does anyone know what is wrong and how it might be fixed? Thanks, Candy What version of Outlook? What type of email account? It sounds like the address book is corrupt. If you use outlook 2003 or older, try this: go to Tools | Email Accounts, choose Vie...

text disapearing in a text box
I put a text box on a chart. After typing it in (it was a long text) I saved the chart I could still see all the text. After I saved it, I created a PDF. It was still fine. After I closed excel. The next time I opened the chart part of the text was gone. I tried to resize the text box, but part of the text was still gone. When I oringinally save the chart I didn't get an error message about the size and the pdf after the save showed all of the text. Thanks ...

format date
I am working on a spreadsheet that someone else created. The dates are set as e.g. 13.03.09 - I want this to read 13/03/09 I have tried formatting cells to date dd/mm/yy and clicking ok but it doesn't alter anything. Apart from going in and altering each cell individually nothing else seems to work. Please help -- Kaz Karray wrote: > I am working on a spreadsheet that someone else created. The dates are set > as e.g. 13.03.09 - I want this to read 13/03/09 I have tried formatting cells > to date dd/mm/yy and clicking ok but it doesn't alter anything. Apart from &...