Countif ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
Hi I'm trying to do a countif function (although that does exist in access), but something else should be fairly simple...
I'm in a 'Totals Query' where I am grouping by date but want to do a count on a field (not a primary key) where that field is not null, "", or value = 0. I tried restricting the the total: Count in the criteria expression but it doesn't work... should i be trying to run a sub query? ..

MyCount: (SELECT [Data Entry] from [Dispatch_All] where [Data Entry] is not null Or [Data Entry] Is Not Null Or [Data Entry]<>"";) ??

any ideas?

thanks,
Marcus
 
you should be able to use an expression in the Totals query, as

ShowCount: Sum(IIf([FieldName] IsNotNull, 1, 0))

yes, i know i used Sum not Count, but Count would count all fields whether 1
or 0, whereas Sum will add up all the 1's.

in the QBE grid, add the above expression to the Field: row of the first
empty column. on the Total: line, select Expression (instead of Group By,
Count, etc.) from the droplist.

hth


marcus. said:
Hi,
Hi I'm trying to do a countif function (although that does exist in
Click to expand...
access), but something else should be fairly simple...
I'm in a 'Totals Query' where I am grouping by date but want to do a count
Click to expand...
on a field (not a primary key) where that field is not null, "", or value =
0. I tried restricting the the total: Count in the criteria expression but
it doesn't work... should i be trying to run a sub query? ..
MyCount: (SELECT [Data Entry] from [Dispatch_All] where [Data Entry] is
Click to expand...
 
Marcus,

If you are doing a Totals Query in the query design view, just put the
criteria in the column of the field you are talking about, and put Where
in the Totals row of this column, and then you can put the Count in a
second instance of the same field in the query design grid if you like,
or else any other field (I guess it is kinda traditional to use the
primary key field for this, but it doesn't really matter - you are
actually counting records regardless of the field). Hope that makes sense.
 
you're welcome.
btw, with my suggestion, you should get a record for each date in the table,
and if all the records for a given date don't meet the criteria in the IIf
function, the ShowCount field should return zero (0) for that date.
with Steve's suggestion, the query will pull records that meet the criteria
in that field, and count them. so if all the records for a given date do not
meet the criteria, that date will be excluded from the query results.
not sure which you wanted to accomplish, but i think Steve and i have
covered everything between us. :)


marcus. said:
so simple!

Thanks Tina, I'm feeling dumb, i should have thought of that!

marcus.

tina said:
you should be able to use an expression in the Totals query, as

ShowCount: Sum(IIf([FieldName] IsNotNull, 1, 0))

yes, i know i used Sum not Count, but Count would count all fields whether 1
or 0, whereas Sum will add up all the 1's.

in the QBE grid, add the above expression to the Field: row of the first
empty column. on the Total: line, select Expression (instead of Group By,
Count, etc.) from the droplist.

hth


marcus. said:
Hi,
Hi I'm trying to do a countif function (although that does exist in
Click to expand...
access), but something else should be fairly simple...
I'm in a 'Totals Query' where I am grouping by date but want to do a
Click to expand...
count
on a field (not a primary key) where that field is not null, "", or value =
0. I tried restricting the the total: Count in the criteria expression but
it doesn't work... should i be trying to run a sub query? ..
MyCount: (SELECT [Data Entry] from [Dispatch_All] where [Data Entry]
Click to expand...
is
not null Or said:
any ideas?

thanks,
Marcus
Click to expand...
Click to expand...
Click to expand...
 
dtoney said:
Is the function IsNotNull available in version 9.0 of Access 2000?
When I try to use it, I get an error message.... that I may have
entered an operand without an operator.
Click to expand...

Don't use IsNotNull. It should be...

Is Not Null

(three words)
 
Back
Top