MS Access Help...Count function

[ryanpch]

Hey,

Not sure if I'm in the right Forum, but here it goes. I thought I knew what I was doing apparently not. I want to count the number of times a 'word' appears in a column. Let's say I have a table called "January1", a column called "ItemCategory". I want to 'count' the number of times 'word1' and 'word2' appears, and total them at the bottom of the table/report. One count for word1 and another count for word2, in seperate text boxes.

Is this on the right track?...=COUNT([ItemCategory])

Thanks in advance.

 

[formerTexan]

I presume you are using the function in a report as the Control Source of a text box. However it is time for a course correction.

Count() will return the number of records in a field that have a value. Otherwise the function knows nothing about the value (whether it is word1 or word2).

Instead use the DCount() function.

=DCount("[ItemCategory]","tblTableName","[ItemCategory] like '*Word1*'")

or

=DCount("[ItemCategory]","tblTableName","[ItemCategory] ='Word1'")

Cheers, Bill

 

[lameid]

formerTexan's solution returns the number of records having Word1 in them not the number of occurences...

First you need a function that will count the occurences of a word... Access does not have a native function but it is easy to write one...

Function Occurences(strIn As String, strTest As String) As Long
    If InStr(1, strIn, strTest) = 0 Then
        Occurences = 0
    Else
        Occurences = UBound(Split(strIn, strTest))
    End If
End Function

Just copy and paste that code into a module and save it.


Next use this function and sum the values. The below is an example of a Control source for the Word1 Example

=Sum(Occurences(ItemCategory, "Word1"))

 

[ryanpch]

Yes that worked!! Thanks Bill, you are the man!

Ryan

 

[formerTexan]

Ryan,

Lameid is absolutely correct. In addition to his admonition about counting records vs counting occurences, you can also add a bit more complexity to your search and count.

You can count "rat" when it is the entire string,
or when it is a word within a string "The rat ran"
or when it is part of a word within a string "I would rather".

Have fun deciding.

Cheers, Bill

 

[dhookom]

You don't really need a separate function. Assuming you want to search a field named Description for a word or phrase entered by the user:

Appears: (Len([Description])-Len(Replace([Description],[Search For],"")))/Len([Search For])

Actually I would use a control on a form for the search for.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Another clever solution Duane!

Although formerTexan definitely has a point about counting the word versus the occurence of a string, the weakness in all the solutions.

To fix you would have to loop through and find all instances of the string using instr and use Mid and Len to determine if the preceding and following characters are whitespace, punctuation, the end of the string, etc. At least I think that is the best solution to handle that.

 

[ryanpch]

So assuming I used this coding and it worked....
=DCount("[ItemCategory]","tblTableName","[ItemCategory] ='Word1'")

Now I need to have one function count several different words and their occurance...how do I end this..for example

=DCount("[ItemCategory]","tblTableName","[ItemCategory] ='Word1' AND 'Word2' AND 'blank' AND 'Word3'")

DO I need to have 'OR' in there as well?

Thanks again,

Ryan

P.S Keep it simple

 

[lespaul]

=DCount("[ItemCategory]","tblTableName","[ItemCategory] ='Word1' AND [ItemCategory]='Word2' AND [ItemCategory]='blank' AND [ItemCategory]='Word3'")

so this will give you a count of 1 if ItemCategory is this:
[tt]
Word1 Word2 blank and Word3[/tt]

if you mean a blank space you'll need to change blank to ' '

all 4 items will need to be in ItemCategory for it to count it one time.

Leslie

Come join me at New Mexico Linux Fest!

 

[lameid]

ryanpch,

I think you mean you want to match ANY of the Words instead of ALL of them.

If I am right, change all the AND's in Leslie's solution to OR; otherwise stick with Leslie's solution.

 

[ryanpch]

Thank you Leslie and Lameid. Yes I need to match any of them and count it in the total. I tried 'AND' as well as 'OR' and couldn't get either of them to work. It looks like this...

=DCount("[QueueName]","frmJanuary1","[QueueName] ='New Enquiries' OR [QueueName]='Closed' OR [QueueName]='Ready To Quote' OR [QueueName]=' '")

This column also has other 'words' in it, such as 'quoted' and 'ordered'. So I tried adding...

NOT [QueueName]='Ordered' NOT {QueueName]='Quoted'")

didn't work though...thoughts?

 

[lameid]

Oops... use like and wildcards. Also I find it odd you have a table or query named frmJanuary1...

=DCount("[QueueName]","frmJanuary1","[QueueName] Like '*New Enquiries*' OR [QueueName]  Like '*Closed*' OR [QueueName] Like '*Ready To Quote*' OR [QueueName] =' '")

I left the last one alone... I'm not sure what you are doing with the space?

 

[ryanpch]

Thanks, I'll give it a shot. Ya, this started out to be an experiment, so the details at the beginning were overlooked, such as the table name. Now it's become a project! To late to change it now! (Maybe)

Thanks again!