Replace([fieldX]....

[bmal]

Good afternoon.

Here is the data I am evaluating:

07/17/2013 8:08AM - Mailed Disclosures to Applicant - Bsmith08/21/2013 4:58PM - Do not have correct address - Jhayes09/13/2013 3:17PM - Do not have correct mailing address - Arogers

This is 3 separate conversation entries.
I am trying to force a carriage return and line feed with something like this:

Conv: Replace([Convo_Log],"*[? #]*",Chr(13) & Chr(10))

In lay terms....I think the best breaking point is between the 'alpha' and 'numeric' characteers....last letter of name AND first number of the date.

Any thoughts? I've done this before with success...but there was always a pipe or some other nice character.

Thanks.

 

[bmal]

Achoo. Bump.

So the question is....am I on the right track if I want to insert a carriage return when the a letter is followed by a number with no spaces. Or maybe someone sees a different place or way to break this string up into 3 lines.

Thanks.

 

[MarkK]

How did this string get lumped together in the first place? Presumably these are separate records. Why combine them and then have to un-combine them? Draw them directly from the table where they will be in distinct rows, and your capacity to separate them reliably will be far simpler and much more robust.

 

[bmal]

That is a very good question. I presume that the Founding Fathers of this industry software did not foresee anyone attempting to extract the information cleanly.

This is a field from a single record in a conversation log table. Sadly, that is how it is stored in the table.

The file gets appended as new convo log entries are created. A new record is not created for each convo log entry.

It looks great in the software program....just now thru odbc.

 

[MarkK]

You suggest there was something the Founding Fathers didn't foresee? OMG, that's blasphemy, or, maybe you're not American?

If those hyphens are always present like that you can use Split() to chop up that string into an array. Then you can step through that array and process each element, the first will be a date, next will be a message, and then next you have to tease apart. I would split it from the left, since the date will always be one of two lengths . . .

dd/mm/yyyy h:nnPM
dd/mm/yyyy hh:nnPM

. . . So I'd chop off the longer one . . .

Right(text, len("dd/mm/yyyy hh:nnPM"))

. . . and check if the first char is a number, and if it's not, the first char is actually the last char in the name. And that's where you insert the new line.

Does that make sense? First thing though, is check out the Split() function.

 

[WayneRyan]

b,

The data does need to be separated AND put into a child table.
This is a little rough, but ...

Make a new child table:

id AutoNumber
fk ??? 			<-- Foreign key to your table (The PK of your current table)
TheDate DateTime	>
Action Text		> Your 3 fields
Person Text		>

Fill it by:

Select *, fnMakeChildren([PK], [YourCurrentField])
From   YourTable


Public Function fnMakeChildren(PK As Long, TheString As String) As Boolean
Dim varArray As Variant
Dim i As Integer
Dim ptr As Int

varArray = Split(TheString), " - ")
For i = 2 to UBound(varArray) Step 2
    ptr = 1
    While Mid(varArray(i), ptr, 1) < "0" And _
          Mid(varArray(i), ptr, 1) > "9" And _
          ptr <= Len(varArray(i))
        ptr = ptr + 1
        Wend
   CurrentDb.Execute "Insert Into ChildTable (fk, TheDate, Action, Person) " & _
                     "Values (" & PK & ", #" & varArray(i - 2) & "#, '" & varArray(i - 1) & "', '" & Mid(varArray(i), 1, ptr) & "')")
   If Len(varArray(ptr) > ptr Then varArray(i) = Mid(varArray(i), ptr + 1)
   Next i
fnMakeChildren = True

hth,
Wayne

 

[bmal]

that's great stuff. I'll hang it up for the evening to revisit it freshly tomorrow.