• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

Expression in a query

Using query designer I have this code:

TouringCount: Abs(Count([ID]) And [PublicActivityCode]="GT")

Which doesn't work.  I'm trying to count the records (ID) where PublicActivityCode] = "GT" or "ST".

It needs to be an expression because I have more of these types of fields to add.
0
SteveL13
Asked:
SteveL13
  • 3
2 Solutions
 
Bill RossCommented:
Hi,

Change the query to group by
Put [ID] as a count field in the query
Make criteria  [PublicActivityCode]="GT"

Regards,

Bill
0
 
Bill RossCommented:
Hi,

You can also create a field using the expression

dcount("ID",yourtable,"PublicActivityCode='GT'")

but this will be slower.

Regards,

Bill
0
 
Bill RossCommented:
FYI - ABS is not required.  Count can never be <0.
0
 
Gustav BrockCIOCommented:
You can use:

TouringCount: Sum(Abs([PublicActivityCode]="GT" Or [PublicActivityCode]="ST"))

/gustav
0
 
PatHartmanCommented:
Functions in a query operate over the domain of the recordset.  So, Count(ID) returns a count of non-null values of ID contained in the recordset.  It cannot be constrained by criteria.  The simplest way to get a count of all values for a particular field is with this type of query:

Select PublicActivityCode, Count(*) As RecCount
From YourTable
Group By PublicActivityCode;

You will get a list like:
GT, 2345
ST, 123
AB, 445
xx, 3939
, 2
, 3

Notice the last two.  One will be the count of null values and the other will be the count of ZLS values.

If you want all the values returned as a single row, create a crosstab of the totals query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now