locked
count within a text field RRS feed

  • Question

  • Although i have seen similar questions, I don't know if anyone has come up with a useful solution for something like this.

    Within a tabel within an Access 2007 database I have a memo field (Rules) where the field may look like the following; "|24923|24924|24921|20030|20040|20050|20060|20070|20080|20090|24203|". I am trying to run a query in Access that will count all of the text string within the pipes. In this example there would be 11 occurences.

    More advanced would be a way to show how may occurences of "24923" occured within the recordset meaning that if I had 20 rows returned and 7 of them had "24923", it would give a a count of 7 for that string. I think I can solve the second question myself but I am stumped as to the answer to the first question.

    Any suggestions?

    Don

    Wednesday, July 6, 2011 9:30 PM

Answers

  • Messages are truncated sometimes.  Without LEN function working you have a problem.

    Suggest start new post for that predicament.

    • Marked as answer by Barely_There Thursday, July 7, 2011 6:36 PM
    • Unmarked as answer by Barely_There Thursday, July 7, 2011 6:36 PM
    • Marked as answer by Barely_There Thursday, July 7, 2011 6:36 PM
    Thursday, July 7, 2011 5:40 PM

All replies

  • Try this --

    Len([MemoField])-Len(Replace([MemoField],"|",""))-1

    Wednesday, July 6, 2011 9:50 PM
  •  

    SELECT Count(*) AS Occurence

    FROM YouTable

    WHERE YourTable.MemoField Like "*24923*";

         OR this -- 

    SELECT [Enter string] AS MyCountString, Count(*) AS Occurence

    FROM YouTable

    WHERE YourTable.MemoField Like "*" & [Enter string] & "*"

    GROUP BY  [Enter string];


    Wednesday, July 6, 2011 9:57 PM
  • Thanks Karl,

    I will try both of your replies. Quick question - would i put your first answer (

    Len([MemoField])-Len(Replace([MemoField],"|",""))-1) in the "Build" expression within the query?

    Thursday, July 7, 2011 4:08 PM
  • In the query design view grid in a blank FIELD row --

    Something_Count: Len([MemoField])-Len(Replace([MemoField],"|",""))-1)

    Thursday, July 7, 2011 4:16 PM
  •  I can not get this thing to work. It keeps throwing an error

     

    Rule_Count: Len([dbo_A_S_Account]![Rules])-Len(Replace([dbo_A_S_Account]![Rules],"|",""))-1

    Can you see something wrong with this?

    Thursday, July 7, 2011 4:34 PM
  •  I can not get this thing to work. It keeps throwing an error

     

    You must tell what the results are instead of just saying 'not work'.  Wrong count? No count? Some error message?

    Try this  --

    Rule_Count: Len([dbo_A_S_Account].[Rules])-Len(Replace([dbo_A_S_Account].[Rules],"|",""))-1

    If not work, the test it piece by piece --

    Replace([dbo_A_S_Account].[Rules],"|","")

    Len(Replace([dbo_A_S_Account].[Rules],"|",""))

    Thursday, July 7, 2011 4:47 PM
  • Here is what comes back:

    Compile error, in expression 'Len([dbo_A_S_Account].[Rules])-Len(Replace([dbo_A_S_Account].[Rules],"|",""))-'.

    It did not include the "1" at the end. Not sure why - may only have so many characters it can display.

    Meanwhile I can't even get the Len function to work so there may be something else going on.

    Thank you for your help!

    Thursday, July 7, 2011 5:31 PM
  • Messages are truncated sometimes.  Without LEN function working you have a problem.

    Suggest start new post for that predicament.

    • Marked as answer by Barely_There Thursday, July 7, 2011 6:36 PM
    • Unmarked as answer by Barely_There Thursday, July 7, 2011 6:36 PM
    • Marked as answer by Barely_There Thursday, July 7, 2011 6:36 PM
    Thursday, July 7, 2011 5:40 PM
  • This is my second reply. the first one got wiped out when I marked your reply as Answered. What happend is that I tried to set up a module with a public function and, in doing so, hosed up Access. I knew something was wrong when I tried running a query with another function in it and it did not work. I took out the offending module and reran the test query. When it worked then I knew that Access was fixed. After that I inserted your solution and it worked.

    Thanks for your help Karl.

    Don

    Thursday, July 7, 2011 6:39 PM