Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting characters in 1 field

    WinNT/Access 97
    I am trying to count the number of semi colons in a field and write that number to another field.
    i.e. Update field 2 as follows.

    Field1 Field2
    a;b;c;d; 4

    Any help would be much appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Counting characters in 1 field

    Do you actually want the number of semicolons or the number of values delimited by the semicolon? Here's a piece of code from the MOD 2000 Code Librarian that will count occurrences of characters:
    <pre>Function CountOccurrences(strText As String, _
    strFind As String, _
    Optional lngCompare As VbCompareMethod) As Long
    'From CodeLibrarian
    ' Count occurrences of a particular character or
    ' characters.
    ' If lngCompare argument is omitted,
    ' procedure performs binary comparison.

    Dim lngPos As Long
    Dim lngTemp As Long
    Dim lngCount As Long

    'Specify a starting position. We don't need it the first
    'time through the loop, but we'll need it on subsequent
    'passes.
    lngPos = 1
    ' Execute the loop at least once.
    Do
    ' Store position at which strFind first occurs.
    lngPos = InStr(lngPos, strText, strFind, lngCompare)
    ' Store position in a temporary variable.
    lngTemp = lngPos
    ' Check that strFind has been found.
    If lngPos > 0 Then
    ' Increment counter variable.
    lngCount = lngCount + 1
    ' Define a new starting position.
    lngPos = lngPos + Len(strFind)
    End If
    ' Loop until last occurrence has been found.
    Loop Until lngPos = 0
    ' Return the number of occurrences found.
    CountOccurrences = lngCount
    End Function</pre>

    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting characters in 1 field

    Thanks for your reply Charlotte,
    I was after the actual number of semi colons in a bid to help me with the other problem (Selecting data from multiple fields). I think the code given should enable me to get the count I need, thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •