How to count number of entries for a non exact keyword

[tronic]

Hi any help would be great. I have a field that has the name neg or nega and
I wish to get a count of the number of entries containing either neg or nega
in that field. Is this possible? And if so, how would I write the quiry?

 

[Stefan Hoffmann]

hi,

Hi any help would be great. I have a field that has the name neg or nega and
I wish to get a count of the number of entries containing either neg or nega
in that field. Is this possible? And if so, how would I write the quiry?

The easy way:

DCount("*", "yourTable", "fieldName LIKE '*neg*'")

But this will also count e.g. negative. When you need an exact match,
then you need some non trivial coding.


mfG
--> stefan <--

 

[Ken Sheridan]

As Stefan has shown you, the LIKE operator can be used, but are the two
possible values legitimate, i.e. does each mean something different, or are
they merely inconsistent versions of the same term? If the latter you should
aim to remove the inconsistency which you can do by changing them all to the
same with an 'update' query, e.g.

UPDATE YourTable
SET YourField = "neg"
WHERE YourField = "nega";

to change them all to 'neg'.

To enforce consistency you should really have another table in which 'neg'
appears just once in a single row, along with other unique values in other
rows. By relating this to your current table and enforcing referential
integrity only legitimate values can be entered into the column in your
current table.

When entering new data into your current table you can then use a combo box
in your data entry form to select a value from the 'referenced' table.

Ken Sheridan
Stafford, England

 

[John Spencer]

One method
DCount("*","YourTableName","YourFieldName in('neg','nega')")

In a query that returns only the count
SELECT Count(*) as NegCount
FROM YourTable
WHERE YourFieldName in ('neg','nega')

In a query that returns other fields you would use something a bit more
complicated.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

Hi any help would be great. I have a field that has the name neg or nega and
I wish to get a count of the number of entries containing either neg or nega
in that field. Is this possible? And if so, how would I write the quiry?

I see you've gotten some good answers but you may need to clarify your
question. Does the field contain only one of the values "neg" or "nega"? Or do
you also want to have "negative", "renege", or "xykgnegabvwx" as hits?

 

[tronic]

Hi thanks, your answers have been great. I only have the two different
entries so I have no problems with hits from negative or xyzneg. I am still
have a problems due to my lack of knowledge. It is a bit embarrassing to ask
but do I enter the
DCount("*", "yourTable", "fieldName LIKE '*neg*'") etc etc text in the SQL
query or as an expression in the design view or somewhere else entirely?
Please excuse my naivety. Thanks again I can’t believe the speed and
knowledge of your replies.

 

[John W. Vinson]

Hi thanks, your answers have been great. I only have the two different
entries so I have no problems with hits from negative or xyzneg. I am still
have a problems due to my lack of knowledge. It is a bit embarrassing to ask
but do I enter the
DCount("*", "yourTable", "fieldName LIKE '*neg*'") etc etc text in the SQL
query or as an expression in the design view or somewhere else entirely?

Depends on where you want to use it! I'd start by putting the expression in a
vacant Field cell in the query grid, or (equivalently) editing the SQL to
include it in the SELECT clause:

SELECT thisfield, thatfield, DCount("*", "yourTable", "fieldName LIKE
'*neg*'") FROM yourtable WHERE...

Please excuse my naivety. Thanks again I can’t believe the speed and
knowledge of your replies.

Thanks. We've had some practice...

 

[tronic]

Hi sorry to keep on but I still can't seem to get this to work. I've entered
DCount("*","Santuary General Membership survey","General comments LIKE
'*neg*'") into the vacant field cell in the query grid and a number of other
combinations but it still won't work. I pulling out my hair but that doesn't
seem to help. If you could offer any more advice I would really appreciate
it. It says syntax error (missing operator) in query expression "General
comments LIKE '*neg*'". My table is Santuary General Membership survey and my
field General comments

 

[tronic]

Hi Thanks for your help. I ran a very similar query for a different field in
the same table and it worked. The euphoria was so overwhelming I had to leave
the office. I’m not sure why it fails to work in this field but I will
persevere. Thanks again!!!!

 

[John W. Vinson]

Hi sorry to keep on but I still can't seem to get this to work. I've entered
DCount("*","Santuary General Membership survey","General comments LIKE
'*neg*'") into the vacant field cell in the query grid and a number of other
combinations but it still won't work. I pulling out my hair but that doesn't
seem to help. If you could offer any more advice I would really appreciate
it. It says syntax error (missing operator) in query expression "General
comments LIKE '*neg*'". My table is Santuary General Membership survey and my
field General comments

Whenever you (unwisely) use blanks or other special characters in table or
fieldnames, you must enclose them in brackets. Try

DCount("*","[Santuary General Membership survey]","[General comments] LIKE
'*neg*'")

Otherwise Access sees the blank after General, recognizes it as the end of
something, and then can't make any sense of the word "comments".