Solved

Expression in a query

Posted on 2014-01-22
5
200 Views
Last Modified: 2014-01-22
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
Comment
Question by:SteveL13
  • 3
5 Comments
 
LVL 14

Expert Comment

by:Bill Ross
Comment Utility
Hi,

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

Regards,

Bill
0
 
LVL 14

Expert Comment

by:Bill Ross
Comment Utility
Hi,

You can also create a field using the expression

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

but this will be slower.

Regards,

Bill
0
 
LVL 14

Expert Comment

by:Bill Ross
Comment Utility
FYI - ABS is not required.  Count can never be <0.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
Comment Utility
You can use:

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

/gustav
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
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

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now