VBA code to insert a CR/LF in a memo field based on search string

[trishcollins]

I import a CSV field which has not preserved the CR/LF when it was exported from BCM Remedy. There is no setting for that. The memo field prints on my report like this:

2012/05/23 3:13:45 PM PCOLLINS This is not considered an emergency, but should be reviewed in more detail to see if this site does require an upgrade PRIOR to the National rollout. A custom solution would be developed, that once implemented, could become the standard product in similar situation. Normally would assign to Network Engineering, but will work with Ray and the IPT Team to cost out the solution and get approval to proceed. 2012/05/24 10:44:28 AM PCOLLINS Sent to Ray Massie for review to determine if a solution needs to be proposed, or if they can wait for the National IPT solution to be ready in 2013.

I want it to print list this:

2012/05/23 3:13:45 PM PCOLLINS This is not considered an emergency, but should be reviewed in more detail to see if this site does require an upgrade PRIOR to the National rollout. A custom solution would be developed, that once implemented, could become the standard product in similar situation. Normally would assign to Network Engineering, but will work with Ray and the IPT Team to cost out the solution and get approval to proceed.

2012/05/24 10:44:28 AM PCOLLINS Sent to Ray Massie for review to determine if a solution needs to be proposed, or if they can wait for the National IPT solution to be ready in 2013.

I want to add VBA code that inserts a CR/LF in the memo field before all but the first occurance of a string that looks like a date, the first occurance doesn't need it. I will do it right after I import the CSV file into the table, so it happens only once, and it always prints and displays the CR/LF. Can anyone provide the code?

The memo field is called "NBS Update" and the table is called "CCRR Remedy Data"

Here is what I have, but don't actually know what to put in to find the date and add a CR/LF:

Dim db As DAO.Database[/FONT]
[FONT=Segoe UI]Dim db As DAO.Recordset[/FONT]
[FONT=Segoe UI]Dim srtSQL As String[/FONT]
[FONT=Segoe UI]Set db = CurrentDb()[/FONT]
[FONT=Segoe UI]StrSQL = "Select 'Last NSB Update' FROM 'Remedy CCRR Data'"[/FONT]
[FONT=Segoe UI]Set rst = db.OpenRecordset(StrSQL, dbOpenDynaset)[/FONT]
[FONT=Segoe UI]With rst[/FONT]
[FONT=Segoe UI]If .RecordCount > 0 Then[/FONT]
[FONT=Segoe UI].MoveFirst[/FONT]
[FONT=Segoe UI].Edit[/FONT]
[FONT=Segoe UI][B][I]'Add something here to find all occurances of the "date" (except the [/I][/B][/FONT]
[FONT=Segoe UI][FONT=Segoe UI][B][I]'first) in the memo field and put a CR/LF in front[/I][/B][/FONT]
[/FONT][FONT=Segoe UI].Update[/FONT]
[FONT=Segoe UI]End If[/FONT]
[FONT=Segoe UI]End With

Thanks in advance

 

[CJ_London]

You can use the replace function - but the trick will be to ensure you identify the target correctly so something like

myNewMemo=replace(myOldMemo, ". 2","." & vbcrlf & vbcrlf & "2")

will work providing all years are 2k and nothing relating to the last century and the preceding sentence ends with a full stop followed by a space. So

"...approval to proceed 2012/05/24 10:44:28 AM... "

would not be identified.

However it would still insert vbcrlf if you had a couple of sentences like

"..and that was the end of that. 2000 saw a change..."

However I notice your original has the dates in bold - is this really the case? if it is then you can use the bold character to identify the target.

 

[trishcollins]

Thanks!

The bold was just so you could see the dates. They are not in bold in reality.

All dates are in the 2000, nothing before 2009 actually.

I cannot guarantee there is a full stop and space ". ", before each occurance of the date, but there is a pretty good chance.

Unfortunately, there are many times when the user has entered a year in the update field, but again, chances are it's NOT right after a ". ", so I will take a risk

 

[trishcollins]

myNewMemo=replace(myOldMemo, ". 2","." & vbcrlf & vbcrlf & "2")

A couple of newie questions:

Do I need to DIM myNewMemo and MyOldMemo or are you referring to the fieldname "NBS Update"

 

[CJ_London]

You probably actually want to do it in a query - which might be the query you use to insert the table or a separate update query.

If an update query it would be

UPDATE [CCRR Remedy Data] SET [NBS Update]=replace([NBS Update], ". 2","." & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "2")

Since you provided the names, you can just copy and paste this into the SQL window of the query builder - in a query you can't use vbcrlf but have to supply the separate characters

 

[nanscombe]

When trying to detect strings like dates I create a fingerprint where letters are replaced by "A", numbers are replaced by "N", other characters replaced as required and everything else as a ".".

Public Function strFingerprintGet(byval theString as String)
Dim lngIndex as Long, strTemp as String, strChar as String

  For lngIndex = 1 to Len(theString)

    strChar = Lcase(Mid(theString, lngIndex, 1))

    Select case strChar
    Case "a" to "z"
      strTemp = strTemp & "A"
    Case "0" to "9"
      strTemp = strTemp & "N"
    Case ":", " ", "/"
      strTemp = strTemp & strChar
    Case else
      strTemp = strTemp & "."
    End Select

  Next lngIndex

  strFingerprintGet = strTemp
End Function

I would then look for location of "NNNN/NN/NN NN:NN:NN AA" in the fingerprint string and use that to form a new string from the text in the original string.

 

[trishcollins]

@CJ London: I tried the query. It ran, but came back with errors. The problem I have with that is that I run several queries BEFORE I run the different reports, and the reports are based on different queries, so I really need the original file to be modified on import or I have to change ever single one of my queries, or add the new query file into each of the others. I realize I could create a query, run the SQL and then copy the file over the original, but it seems like a lot of work.

@nanscombe: Interesting. I understand the concept, and it's basically what I am looking for, but it's out of context for me, and I wouldn't know how to add it to my initial Event Procedure which runs when I invoke a particular form. I need someone to provide code that contains my file names and my field names or it's all Greek to me.

Cheers, Trish

 

[jdraw]

I import a CSV field which has not preserved the CR/LF when it was exported from BCM Remedy. There is no setting for that. The memo field prints on my report like this

A CSV field or an entire file?

Do you process or store the CSV data in a table?
Tell us more about the CSV file, and the fields.

Why are you storing this in a single memo field?
Perhaps it should be a memo field in a separate table where each record has some sort of Datefield.

 

[nanscombe]

@nanscombe: Interesting. I understand the concept, and it's basically what I am looking for, but it's out of context for me, and I wouldn't know how to add it to my initial Event Procedure which runs when I invoke a particular form. I need someone to provide code that contains my file names and my field names or it's all Greek to me.

Cheers, Trish

Currently putting together a demo. Bear with me.

 

[trishcollins]

A CSV field or an entire file?

Do you process or store the CSV data in a table?
Tell us more about the CSV file, and the fields.

Why are you storing this in a single memo field?
Perhaps it should be a memo field in a separate table where each record has some sort of Datefield.

Sorry, I meant "File". I export the file from Remedy in CSV format. It will not preserve the CR/LF. I know they are there, because if I open the record in Remedy, it does a CR/LF before each of those dates.

 

[trishcollins]

Currently putting together a demo. Bear with me.

Greatly appreciated, Thanks!

 

[nanscombe]

In the attached database I've managed to package the functionality into two functions:

strFingerprintGet() - Takes a string as a parameter and returns a string of "A", "N" etc.

addCrLfBeforeDate() - Takes a string and returns a string with added CRs, uses strFingerprintGet() for create a fingerprint in order to look for dates.

By packaging it up in this way it should be fairly straightforward to use.

It should be as simple as ...

strAfter = addCrLfBeforeDate(strBefore)

Take a look and see what you think.

 

[trishcollins]

Thanks, I will give it a try and let you know how it goes!

 

[nanscombe]

An unzipped version (260Kb), if that's of use, and the code of the two functions.

Public Function strFingerprintGet(ByVal theString As String)
Dim lngIndex As Long, strTemp As String, strChar As String

  For lngIndex = 1 To Len(theString)

    strChar = LCase(Mid(theString, lngIndex, 1))

    Select Case strChar
    Case "a" To "z"
      strTemp = strTemp & "A"
    Case "0" To "9"
      strTemp = strTemp & "N"
    Case ":", " ", "/"
      strTemp = strTemp & strChar
    Case Else
      strTemp = strTemp & "."
    End Select

  Next lngIndex

  strFingerprintGet = strTemp
End Function

Public Function addCrLfBeforeDate(ByVal theString As String)
Dim strTemp As String, lngFound As Long, strResult As String
Dim strFingerPrint As String

strFingerPrint = strFingerprintGet(theString)

strResult = vbNullString
strTemp = theString
lngFound = 0
Do
    lngFound = InStr(10, strFingerPrint, "NNNN/NN/NN NN:NN:NN AA")
    If lngFound = 0 Then
      If Len(strResult & vbNullString) > 0 Then strResult = strResult & vbCrLf & vbCrLf
      strResult = strResult & strTemp
      Exit Do
    End If
    
    If Len(strResult & vbNullString) > 0 Then strResult = strResult & vbCrLf & vbCrLf
    strResult = strResult & Mid(strTemp, 1, lngFound - 1)
    strTemp = Mid(strTemp, lngFound)
    strFingerPrint = Mid(strFingerPrint, lngFound)
Loop

addCrLfBeforeDate = strResult
End Function

This is how I call it, source in textbox (theMemo), result in textbox (txtResult).

Me.txtResult = addCrLfBeforeDate(Me.theMemo)

 

[nanscombe]

Before:

2012/05/23 3:13:45 PM XXXXXXXX This is not considered an emergency, but should be reviewed in more detail to see if this site does require an upgrade PRIOR to the National rollout. A custom solution would be developed, that once implemented, could become the standard product in similar situation. Normally would assign to Network Engineering, but will work with Ray and the IPT Team to cost out the solution and get approval to proceed. 2012/05/24 10:44:28 AM XXXXXXX Sent to XXX XXXXXX for review to determine if a solution needs to be proposed, or if they can wait for the National IPT solution to be ready in 2013.

After

2012/05/23 3:13:45 PM XXXXXXX This is not considered an emergency, but should be reviewed in more detail to see if this site does require an upgrade PRIOR to the National rollout. A custom solution would be developed, that once implemented, could become the standard product in similar situation. Normally would assign to Network Engineering, but will work with Ray and the IPT Team to cost out the solution and get approval to proceed.

2012/05/24 10:44:28 AM XXXXXX Sent to XXX XXXXX for review to determine if a solution needs to be proposed, or if they can wait for the National IPT solution to be ready in 2013.


Just replace a couple of names in my databases in case you wanted to preserve a bit of anonymity.

 

[trishcollins]

Thanks!

As I don't see ANY references to either the table name or the field name, where do I put this? I want it to happen when I load this form, as it's the first thing that happens when I open up my Database. It would have to happen right after I import the data to the new table (I delete the existing table first -- it's easier than appending). I bolded that import code. I would assume at that point I need to open the table, and select the field, then run what you have put in. Forgive me, although I understand programming, I don't program very often, and it usually takes me some time to get back up to speed.

Private Sub Form_Load()
'Remove Selection from Time/Date Stamp Listbox
Last_Updated = Null
'Deteremine if version is production or development based on the filename and adding the appropriate watermark to the form
Dim strDbPath As String
strDbPath = CurrentDb.Name
strDbPath = Left(strDbPath, InStrRev(strDbPath, "\", , vbTextCompare))
If CurrentDb.Name Like "*Dev*" Then
       Me.Form.Picture = strDbPath & "Dev_Watermark.bmp"
       Else
       Me.Form.Picture = strDbPath & "Prod_Watermark.bmp"
End If
'Automatically import CSV files from Remedy if the last update is older than the time/date stamp on the export file
Dim RemedyDate As Date
Dim CurrentImport As String
CurrentImport = "Remedy"
On Error GoTo Message
RemedyDate = FileDateTime(strDbPath & "Remedy CCRR Data.csv")
Dim LastRemedyUpdate As Date
LastRemedyUpdate = DLookup("DateUpdate", "SSC-RCMP Table Names", "Name = 'Remedy CCRR Data'")
If RemedyDate > LastRemedyUpdate Then
    MsgBox "Remedy Export File is more current than last update -- Importing Remedy CCRR Data"
    DoCmd.SetWarnings False
    On Error Resume Next
    DoCmd.DeleteObject acTable, "Remedy CCRR Data_ImportErrors"
    DoCmd.DeleteObject acTable, "Remedy CCRR Data"
    On Error GoTo Message
    [B]DoCmd.TransferText acImportDelim, "Remedy CCRR Data Import Specification", "Remedy CCRR Data", strDbPath & "Remedy CCRR Data.csv"[/B]
End If
'
Me.Requery
Me.Refresh
DoCmd.SetWarnings True
End
Message:
If Err.Number = 53 Then
    MsgBox CurrentImport & " file cannot be found for importing"
    If Err.Number <> 7874 And Err.Number <> 0 And Err.Number <> 53 Then
        MsgBox "Problem importing file, please see your adminstrator"
    End If
End If
End Sub

 

[nanscombe]

Since you are doing an import I would probably create an Update query with Field:"theFieldInQuestion" and Update To: addCrLfBeforeDate("theFieldInQuestion") and run it after the DoCmd.TransferText acImportDelim line.

 

[trishcollins]

THANK YOU! This is very cool. I ran your sample an it worked exactly the way I wanted.

In your example, you are running the Fingerprint and Result for display form for one record ONLY with an event procedure attached to the "click me". The raw data remains untouched.

I just need to adapt it to run for all records in the table, and to replace the original memo field (theMemo) with the modified one (theResult).

The exampe was very helpful, as I am a visual person and need to "see" things work. I will let you know how it goes!

Cheers, Trish

 

[nanscombe]

As I say, you need to create an update query to run against your table (Remedy CCRR data?).

When you use the Access query creator on the lower half of the screen will be where you add your fields. You would add the memo field (theFieldInQuestion) where it says "Fields:".

When you choose "update query" you will get an additional option of "Update To:", this is where you put the function together withe the name of the field you specified above, addCrLfBeforeDate("theFieldInQuestion").

Alternatively you could simply write a piece of SQL and run it with a couple of lines of code.

Dim strSQL as String
...
strSQL = "UPDATE [Remedy CCRR Data] SET [theFieldInQuestion] = addCrLfBeforeDate([theFieldInQuestion])"
DoCmd.RunSQL strSQL

I have enclosed the table and field name in [] to get around any spaces in the names.

 

[trishcollins]

@nanscombe

Finally got some time today to play with the database you created. I imported my own data table and then changed "theMemo" field in your tblRawData table to field NBS_Update in the Remedy CCRR Data table. Note that I changed the field name to include the "_" to avoid the space, as I wasn't sure the syntax for me.NBS Update. I would rather deal with the space in the code though, so if you could provide me with the proper syntax for that, that would be great.

I noticed the pattern you have it looking for is "NNNN/NN/NN NN:NN:NN AA", which assumes the system originating the record uses a 24 hour clock. However, the system doesn't, so it is missing anything less than 10 o'clock. Essentially, it needs to look for both the above pattern, and this pattern "NNNN/NN/NN N:NN:NN AA". Is that possible to look for either string at the same time or do I need to repeat the loop a second time?

Interestingly the example I sent you worked because the FIRST occurance of the timestamp had a single digit hour, and since the first occurance doesn't need a CR/LF inserted in front of it, I didn't notice.

Otherwise it seems to work fine. And after I fix this, the next step is to incorporate it the import code for the my form launch.

Otherwise, it works great in your Form Test database.