Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2006
    Posts
    8

    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:

    Field1 | Field2
    B1234 | P#234234;P#90234;P#234897;P#7542389
    B4567 | P#934244;P#20244;P#534897

    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!
    Last edited by rbastedo; 06-07-06 at 15:10.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    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.

    Code:
    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))
      Else
        SubStringCount = UBound(Split(UCase(StringBody), UCase(SubStrg)))
      End If
    End Function
    For now, place a command button on a Form then in the Code Module for OnClick event, copy and past this code:

    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"
      CurrentDb.Execute Strg
     
      ' 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...
      CurrentDb.Execute Strg
      ' 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...

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi rbastedo

    Welcome to the forum

    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

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2006
    Posts
    8
    CyberLynx,
    Wow, very nice! Thank you, I'll see what I can do to make that run.

    pootle flump, thanks for the welcome & the advice - this is a great place!

  5. #5
    Join Date
    Jun 2006
    Posts
    8
    I guess I'm doing something wrong - I keep getting an error that says:

    "The expression On Click you entered as the event property setting produced the following error: Invalid outside procedure."
    Maybe I'm too dim to get this done without more detailed instruction, if so I apologize. If someone wants to help me out a bit more I would very much appreciate it. Thanks

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    Do something for us....

    Start you DB
    Open the Visual Basic Editor (VBE)
    Select the menu Items Debug then Compile yourDBname

    An Error message box may display, select Debug if it does. Which line is highlighted in the editor?


    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  7. #7
    Join Date
    Jun 2006
    Posts
    8
    Thanks for your help (and your patience).

    This is the highlighted line:
    Strg = "ALTER TABLE SLHHOLDS ADD COLUMN FIELD_TWO_PCOUNT Number"

    The error says:

    "Compile Error:
    Invalid outside procedure"

  8. #8
    Join Date
    Nov 2003
    Posts
    1,487
    Thats odd...I can't see why you should get this error. Curious, what version of MS-Access are you dealing with?

    In any case, I have attached a small sample app so that you can see this work.

    .
    Attached Files Attached Files
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  9. #9
    Join Date
    Jun 2006
    Posts
    8
    Thanks, I was ill there for a few days so this is the first time I've been able to get back to this. I appreciate your help.

  10. #10
    Join Date
    Jun 2006
    Posts
    8
    CyberLynx, wow - thanks!
    I can see a lot of places I was heading in wrong directions, no wonder my stuff didn't work.
    Again, your help has been greatly appreciated!!!

  11. #11
    Join Date
    Jun 2006
    Posts
    8
    This is working great for me, I've made some changes & edits and tested against some small to medium size files and everything is going along nicely.

    Is there a way to change the application name in the title bar of a form?
    For instance, the name on the title bar of the form that CyberLynx sent is: "Sample Application". Can that be edited somehow?

    Thanks.

  12. #12
    Join Date
    Nov 2003
    Posts
    1,487
    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.

    Done deal...

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  13. #13
    Join Date
    Jun 2006
    Posts
    8

    Thumbs up

    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

  14. #14
    Join Date
    Nov 2003
    Posts
    1,487
    I glad to hear that this forum has assisted you and others around you with the task at hand.


    We all wait in anticipation for you next question.

    Good luck with all your future endeavours.

    .

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

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