CODEWITHSUNDEEP
vbams-accesstext
inchman2
inchman2

Reputation: 3

Counting text values in a Access Field to generate a value for another field

I have a weird problem I'm trying to solve. I have a table with a text field. In that text field, the same word may appear more than once (on purpose.) I'm not worried about misspellings or variations at this time.

Here is an example... The text field is for which college degrees a person has. A sample record might be... "Bachelors of Science in Geology, Bachelors of Arts in basket weaving, Associates in Science." Now, I want to count how many times "Bachelors" and "Associates" appear in this field. So there would be two additional calculated fields called "Total Bachelors" and "Total Associates." In this case, they would be set to "2" and "1" respectively.

Is this something that is even possible? If so, how can I perform this in a query? Thanks so much for any help. Inchman

Upvotes: 0

Views: 220

Answers (2)

Andre
Andre

Reputation: 27634

To count a word, you need a public VBA function in a standard module:

'--- Counts the number of occurrences of Needle in Haystack ---
' It is always safest to use Variant parameters for functions that called with user data
Public Function CountWord(vHaystack As Variant, vNeedle As Variant) As Long

    Dim sHaystack As String
    Dim sNeedle As String

    If Len(vHaystack) > 0 And Len(vNeedle) > 0 Then
        ' Variant to string
        sHaystack = vHaystack
        sNeedle = vNeedle
        ' 1. Remove all instances of sNeedle in sHaystack
        ' 2. Calculate length difference of original vs. replaced string
        ' 3. Divide this number of characters by the length of sNeedle
        CountWord = (Len(sHaystack) - Len(Replace(sHaystack, sNeedle, ""))) / Len(sNeedle)
    Else
        ' Empty input data
        CountWord = 0
    End If

End Function

Then call that function in your query:

SELECT ID, [Name], [degrees],
  CountWord([degrees], "Bachelors") AS TotalBachelors,
  CountWord([degrees], "Associates") AS TotalAssociates
FROM persons

Upvotes: 2

Sergey S.
Sergey S.

Reputation: 6336

Simplest solution - to create a function in standard module, which returns number of word appearances in string and use it in query field

Upvotes: 0

Related Questions