VBA code to insert a CR/LF in a memo field based on search string (1 Viewer)

[nanscombe]

I remembered that you can't get zip files, hence unzipped Db

Right, I've adjusted the code to deal with single hour digit and double hour digit by checking for both and picking the one closest to the beginning of the string, assuming they exist.

The new function looks like this.

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

strFingerPrint = strFingerprintGet(theString)

strResult = vbNullString
strTemp = theString
lngFound = 0
Do
    
    lngFoundN = InStr(10, strFingerPrint, "NNNN/NN/NN N:NN:NN AA")
    lngFoundNN = InStr(10, strFingerPrint, "NNNN/NN/NN NN:NN:NN AA")
    
    lngFound = Len(strFingerPrint)
    If (lngFoundNN > 0) And (lngFoundNN < lngFound) Then lngFound = lngFoundNN
    If (lngFoundN > 0) And (lngFoundN < lngFound) Then lngFound = lngFoundN
    If (lngFoundNN = 0) And (lngFoundNN = 0) Then lngFound = 0
    
    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

I have also adjusted the table and field names to cater for your situation and included a couple of example queries to show how I manipulate the data.

I update field [NBS Update] (you can wrap [] around names with spaces in them) with the manipulated version of itself (query qryUpdateExample).

I then use the contents field theMemo to reset [NBS Update] to its original version. (query qryResetExample)

I hope this works Ok.

 

[trishcollins]

That worked like a charm! Thanks so much. It's exactly what I needed.

 

[nanscombe]

That's good to hear. Glad to help.

 

[Galaxiom]

Nigel's fingerprint technique is quite creative.

Another possible alternative would be to use a match by Regular Expression.

 

[nanscombe]

I first used the fingerprint idea when faced with getting info out of thousands of digital photos.

Searching for strings like Canon and Casio was pretty straightforward, dates less so.

You could see the datestamp info in the JPG files, via a Hex editor, it was a matter of figuring out a way of being able to extract the data and put it into an Access database.

I now use Lightroom instead.



It also comes in handy if you use filenames with YYYYMMDD somewhere in them and you want to do something based on the date.

For instance:

A filename containing YYYYMMDD_ could be detected and stored in a structure as
Root\YYYY\YYYYMM\YYYYMMDD\YYYYMMDD_Filename.

A filename containing YYYYMM_ as
Root\YYYY\YYYYMM\YYYYMMDD_Filename.

A filename containing YYYY_ as
Root\YYYY\YYYYMMDD_Filename.



The idea also comes in useful for identifying timestamps as "NN:NN" (10:15).

Whilst the numbers may change the fact that they are numbers doesn't.