Use count or countif and exclude certain customer id

[tagteam]

I want to count customerID in a table but I want to exclude 3 id's. Is there a way to do this with count or countif on an access form?

I have tried countif([CustomerId[, Not in (3,8,9))
=Countif([Car Initials],<>3 or <>8 or <> 9)
=Countif([Car Initials],<>3,8,9)



None of those are allowed

Also, when i try to do it in a regular query Not in (3,8,9) works but if I change it to a totals query and use the count function it does not work.

[tagteam]

Silly me, i just had to do the count on another field and add a where condition for the main field

Ex; =DCount("RailcarID","AllRailcarData","CustomerID not in (3,8,9)")

[ranman256]

T1 is your main data table,
T2 holds the keys you dont want
in the query join the 2 tables on key
set to OUTER join, dbl click the join line, set for :ALL recs in T1.
bring down the key field from both tables,
under T2 key, set criteria IS NULL
run query,


you will only get the items NOT in T2 table.
or your way too.

[tagteam]

Interesting, I like that idea of creating a separate table with the values I dont want and then I can just add new values at any time and the report will be updated. I am going to give it a try, thanks

[orange]

@tagteam,
You'll like this tutorial.