Microsoft Access: String and Data Manipulation Programming Examples

' Purpose: Use InStr function to locate the last occurrence of the search string, then replace it with the desired value.

Sub Find_Replace_LastOccurence()
Dim original_string
Dim search_string As String
Dim replace_string
Dim new_string As String
Dim found_position As Integer
Dim last_found_position As Integer

original_string = "strangely_they_think_singapore_is_a_city_of_singapore"
search_string = "singapore"
replace_string = "malaysia"

found_position = InStr(1, original_string, search_string)
' If one occurence exists then keep checking and store each successive found position in last_found_position
If found_position > 0 Then
   last_found_position = found_position
   Do Until found_position = 0
      found_position = InStr(found_position + 1, original_string, search_string)
      If found_position > 0 Then last_found_position = found_position
   Loop
      ' Now use Replace with knowing where last occurence starts
   new_string = Left(original_string, last_found_position - 1) & _
   Replace(original_string, search_string, replace_string, last_found_position)
End If
MsgBox new_string
End Sub

' Purpose: To return the last text, word, one or a continuation of numbers, in the strings of the table field.

Function GetLastPortionOfString(sStr As String) As String
Dim i As Integer, j As Integer
Dim s As String, tmp As String
Dim last_text As String, Hold As String
Dim charFound As Integer

tmp = ""
last_text = ""
charFound = False
Hold = sStr
j = Len(sStr)

For i = j To 1 Step -1
   s = Right(Hold, 1)
   If s = " " Then
      If Not charFound Then
      '* skip spaces at end of string.
      Else
         last_text = tmp
      Exit For
      End If
   Else
      charFound = True
      tmp = s & tmp
   End If

   If Len(Hold) > 0 Then
      Hold = Left(Hold, Len(Hold) - 1)
   End If
Next i

If last_text = "" And tmp <> "" Then
   last_text = tmp
End If

GetLastPortionOfString = Trim(last_text)
'MsgBox "lastword =" & Trim(last_text)
End Function

' Purpose: To count in a table's field on how many upper case and lower case characters, numbers, and other characters.

Sub ImpossibleCounting()
Dim data As String
Dim data_length As Integer, char_loop As Integer, number_count As Integer
Dim upper_case_count As Integer, lower_case_count As Integer, other_count As Integer

Dim conn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sql As String
Dim originalLength As Integer

Set conn = CurrentProject.Connection
sql = "Select * From Table1"
rst.Open sql, conn, adOpenKeyset, adLockOptimistic

number_count = 0
upper_case_count = 0
lower_case_count = 0
other_count = 0

Do Until rst.EOF
   data = rst.Fields("Field1")
   data_length = Len(data)

   For char_loop = 1 To data_length
      ' Asc function returns an integer representing the character code corresponding to the first letter in a string.
      Select Case Asc(Mid(data, char_loop, 1))
         Case 48 To 57
            number_count = number_count + 1
         Case 65 To 90
            upper_case_count = upper_case_count + 1
         Case 97 To 122
            lower_case_count = lower_case_count + 1
         Case Else
            other_count = other_count + 1
         End Select
   Next char_loop

   rst.MoveNext
Loop

Debug.Print number_count & " numbers found"
Debug.Print upper_case_count & " upper case letters found"
Debug.Print lower_case_count & " lower case letters found"
Debug.Print other_count & " other characters found"

rst.Close
Set rst = Nothing
Set conn = Nothing
End Sub