Unanswered: Count occurance of text string in a field / column? - (having trouble applying)
Hi, I've been lurking a bit & just had a question come up so I thought I would join & post
The person I want to help has a table, very simple, 2 columns.
* What version of Access you're using
* The business logic or problem you are trying to solve with your application
* The SPECIFIC behavior or lack thereof that describes your problem
* If you're posting regarding an error, please, post the error. C'mon, seriously folks.
* Post your current solution, what it's doing that you don't like AND an example of your desired results
(ok, following the rules is always a good place to start)
ACCESS 2003 SP2
COUNT OCCURRANCE OF TEXT STRING TO PRODUCE COLUMN WITH SUM
WORKS LIKE A CHARM IN EXCEL - TEXT SRTING TOO LONG FOR EXCEL SO TRYING ACCESS
Anyway here is her simple table:
TABLE NAME: SLHHOLDS
RECORD_NUM (First field - a text identifier)
ITEMS (Second field text/memo - sometimes very long)
She wants to get the number of times a text srting is repeated in that second field. So it looks something like:
What she would really like is to add a third column that automatically fills in the count of number of times the string (P#) occurs in Field2.
Then she could just import her data to that table whenever she does the report and she would automatically get her numbers.
I'm not well versed in Access, I know just about enough to be dangerous.
Any help would be greatfully accepted, other ideas about how to reach the goal would also be happily received. Thanks!
Hmmm...How many times the P# is found in the second table field (ITEM), then place that count into a automatically added third field which I'll name FIELD_TWO_PCOUNT for the sake of this example.
Well, here's one way you can do it....how you implement it is up to you:
You will need the use of a Function to read the data string held within the ITEM table field so as to determine the number of occurences there may be of the "P#" sub-string. The little function I provide below should do nicely. Simply copy and paste it to a Database code module. Not a Form Module, you want reference to this function to be Global to the entire Database.
Public Function SubStringCount(StringBody As String, SubStrg As String, _
Optional IsCaseSensative As Boolean) As Long
' This function will Return the number of occurences
' a the supplied Sub-String (1 or more characters)
' is encountered withing the supplied String Body.
If IsCaseSensative Then
SubStringCount = UBound(Split(StringBody, SubStrg))
SubStringCount = UBound(Split(UCase(StringBody), UCase(SubStrg)))
For now, place a command button on a Form then in the Code Module for OnClick event, copy and past this code:
Dim Strg As String
' Trap Errors
On Error Resume Next
' Create the new FIELD_TWO_PCOUNT column in our table
' using the below SQL string.
Strg = "ALTER TABLE SLHHOLDS ADD COLUMN FIELD_TWO_PCOUNT Number"
' If there is an Error then chances are the Field
' already exists in Table (in case you fire this
' more than once).
If Err <> 0 Then Err.Clear ' Clear off any Errors.
' So let's rip through the table using the SQL
' UPDATE statement and set our FIELD_TWO_PCOUNT
' field to the value of every "P#" sub-String we
' encounter in within the ITEMS column of every
' table row. Notice the SubStringCount function
' is used within this SQL string.
Strg = "UPDATE SLHHOLDS SET FIELD_TWO_PCOUNT=SubStringCount(SLHHOLDS!ITEMS, ""P#"", False);"
' Run the SQL string...
' Done...check your table.
What this will do is, when you select the command button a new field column is created within the SLHHOLDS table named FIELD_TWO_PCOUNT if it doesn't already exist. Then the this new field is filled with the number of times the Sub-String "P#" is encountered within the ITEMS column of each table row.
Hope it helps somewhat...
Self Taught In ALL Environments.....And It Shows!
Wonderfully asked question. Teddy - perhaps this could be linked to as a model question as per FAQ guidelines.
CyberLynx's answer is excellent (I never thought of doing it that way) however there are two aspects to your question that suggest that your friend would be well advised to read and absorb this: http://r937.com/relational.html
Place the form into Design View from the menu bar (menu items: View | Design View) or with the Design View button (blue triangle) from the Form View Tool Bar.
Click on the small square box located in the upper left corner of the form window when in design view. This box is located directly under the form's title bar icon. Normally it is selected by default when the form is placed into design view. You will know when it's selected when a black dot appears in the center of the box.
Now open the Properties window (menu items: View | Properties).
Select the Format Tab located at the top of the Properties window. Notice at the top of the list....the first property is...Caption. See what it says there?
Change it to whatever you like.
You can also do this via code. From within the code module of the Form itself you can use:
Me.Caption = "I Want My Form's Title Bar To State This!!"
or if the form is open behind a second form you can change the name from the second Form's code module like this:
Forms("Form1").Caption = "I Want My Form's Title Bar To State This!!"
Form1 is the name of this Form as seen within the Database Window.
Self Taught In ALL Environments.....And It Shows!
Thanks again CyberLynx, I hope this thread is as helpful to others as it has been to me. With what I have received here I have reduced a friends workload by several hours per week for the forseeable future and I'll be able to use this more in the non profit organization for which I work, increasing all around productivity.
I kind of feel like I should take some classes, I'm located pretty close to Redmond WA after all