Countif

[map816]

This is a follow up question to an older thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=100040&highlight

Can you use this for a field that is concatenated too? You can in excel. I did this in access and it will return a 1 for those fields with only the exact text option in it but if it had the text option I was looking for and then other options in the field with it it returns a 0 for that field when I was hoping for a 1 too since the text was in the field. In excel you can just add "*text*" and it will count the text but it doesn't seem to work in access. Suggestions?

 

[gemma-the-husky]

can you expand this - i dont quite understand what you are asking.

 

[map816]

The link talks about the countif function.

Say I have a field that is concatenated and the rows look like this:

connected
congruent, not connected
non congruent, connected
congruent, connected, non congruent

With this data I want to get tallies of how many times each show up through all the fields in an access query/table. In excel I can get this to work with the =COUNTIF(cellrange,"*connected*"). Excel will properly return the number of times each show up (3) but if you apply this to Access the IIf([fieldname]="connected",1,0) will not return the proper amount of times it shows up in the database. It will return a count of 1 only because the exact text connected only shows up once while the other times had it concatenated with other values. Is there a wildcard option in access too like present in excel to get access to give me a count of 3? Thanks.

 

[Galaxiom]

IIf([fieldname] Like "*connected*",1,0)

However this will also return records that include "not connected" since they also contain "connected". So would Excel example you posted.

Ideally the data structure should be changed to hold these values as individual records in a related table.

 

[map816]

Great. Thank you very much.

 

[vbaInet]

Pretty much a similar question to your other thread, but I think what you're really after is counting the occurence of words within text. The proposed solution will only count an occurence of the word per record.

I've made some changes to your former db and it counts words in two ways. There are few algorithms out there and obviously different ways of counting. Have a look and see if you can spot the differences.