By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,695 Members | 1,597 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,695 IT Pros & Developers. It's quick & easy.

Access - count(iif based on values in 2 fields?

P: 6
I’m having trouble with a report field.

Need it to count records in a table only where BOTH:
1. Field1 – date is less than 1/1/2013; and
2. Field2 – is null

In the table, Fields 1 and 2 are date fields and either contain a date, or are left empty.

I’ve tried the following:

Expression: Count(IIf(([tbl].[field1]<#1/01/2013#) And ([tbl].[field2] Is Null),1,0))

but... not working. Any help would be appreciated!
Jun 17 '14 #1
Share this Question
Share on Google+
12 Replies


Expert 100+
P: 1,240
Your expression says <#1/01/2013# but your description of the problem says IS 1/1/2013. Is the code wrong or the description?

Jim
Jun 17 '14 #2

P: 6
The description is wrong - will edit now
Jun 17 '14 #3

Rabbit
Expert Mod 10K+
P: 12,430
Count doesn't care what it counts. Put your criteria in the where clause instead of using iif.
Jun 17 '14 #4

P: 6
Rabbit, I initially tried to put the criteria in the where clause but Access error message "cannot have aggregate function in where clause" arose.

I did some google searching and came across people using count(iif but none where the iif includes 2 variables (as above).
Jun 17 '14 #5

twinnyfo
Expert Mod 2.5K+
P: 3,485
shooshed,

Have you tried using the DCount Function:

Expand|Select|Wrap|Line Numbers
  1. DCount("*", "tbl", "[Field1] < #1/1/2013# AND [Field2] Is Null)
This has works with my tables....

Hope this hepps!
Jun 17 '14 #6

Rabbit
Expert Mod 10K+
P: 12,430
You don't put the aggregate function in where clause. You only put the filter criteria in the where clause. You leave the aggregate function in the select.

Those examples you saw using count(iif()) are wrong. It doesn't work. If anything, you saw sum(iif()). But while that technically works, it is very inefficient if you don't need to return all the rows. It's better to use the algorithm I laid out in the first paragraph.
Jun 17 '14 #7

NeoPa
Expert Mod 15k+
P: 31,769
@Twinny.
Domain Aggregate functions (DCount()) would be relatively inefficient as they have to enquire of the data separately, whereas Aggregate functions (Count(); Sum(); etc) perform within the context of the existing query so are more efficient. Also bear in mind that any use of a Domain Aggregate function would also need to reflect the current grouping in the filter. Your posted suggestion would count all of the records in the whole table rather than just those appropriate within the current GROUP BY group.

@Rabbit.
Rabbit:
Those examples you saw using count(iif()) are wrong. It doesn't work.
That way they won't, but they can be used if the zero value is replaced with Null.

EG :
Expand|Select|Wrap|Line Numbers
  1. Expression: Count(IIf(([tbl].[field1]<#1/1/2013#) And ([tbl].[field2] Is Null),1,Null))
Of course, the Sum() version will work and may be easier to grasp :
Expand|Select|Wrap|Line Numbers
  1. Expression: Sum(IIf(([tbl].[field1]<#1/1/2013#) And ([tbl].[field2] Is Null),1,0))
Jun 29 '14 #8

twinnyfo
Expert Mod 2.5K+
P: 3,485
@NeoPa,

This may be slightly off-topic, but I think it is related. You said:

Domain Aggregate functions (DCount()) would be relatively inefficient as they have to enquire of the data separately, whereas Aggregate functions (Count(); Sum(); etc) perform within the context of the existing query so are more efficient. Also bear in mind that any use of a Domain Aggregate function would also need to reflect the current grouping in the filter. Your posted suggestion would count all of the records in the whole table rather than just those appropriate within the current GROUP BY group.
Please explain, so that I understand, because it appears that the DCount, Sum and Count all appear (in this case) to look at all records and evaluate according to the same criteria.

The reason I ask is obviously because I wish to have a more efficient DB, and I use DCount numerous times in finding the number of records that meet certain criteria. If I have better, more efficient ways to do this, then, obviously I wish to do that. However, I also wish to understand why one way is more efficient, when, on the surface, they appear identical but use different language.

Thanks!
Jul 2 '14 #9

Rabbit
Expert Mod 10K+
P: 12,430
Domain aggregate functions such as DCount and DSum have extra overhead because they call code for each row (assuming the function call references a field in the recordset).

Whereas Count() and Sum() work with the set of data just the one time.

For example:
Expand|Select|Wrap|Line Numbers
  1. SELECT country, count(*)
  2. FROM tblPopulation
  3. GROUP BY country
Expand|Select|Wrap|Line Numbers
  1. SELECT country, DCOUNT("*", "tblPopulation", "country = '" country & "'")
  2. FROM tblPopulation
  3. GROUP BY country
Assuming there are 10 distinct countries, the DCount version calls the code 10 times. Each time it does a separate count of the data. Basically, it's running 10 separate queries to provide the counts.

The Count version pulls the data once and does the counts.

However, if the domain aggregate function does not reference a field in the recordset, the overhead is negligible because the optimizer runs the function once.

For example:
Expand|Select|Wrap|Line Numbers
  1. SELECT a.country, b.allCount
  2. FROM tblPopulation a, (
  3.    SELECT COUNT(*) AS allCount
  4.    FROM tblPopulation
  5. ) b
Expand|Select|Wrap|Line Numbers
  1. SELECT a.country, DCount("*", "tblPopulation")
  2. FROM tblPopulation
There will be almost no performance difference between the first version and the second. However, I still recommend the first version because of consistency and portability. The first version can be ported more easily if the database is moved to another DBMS.
Jul 2 '14 #10

twinnyfo
Expert Mod 2.5K+
P: 3,485
Rabbit,

As usual, you explained well to a young Jedi. Some day I may fully understand all the secrets of the dark side of the Force.

Thanks for the excellent explanation!
Jul 2 '14 #11

Rabbit
Expert Mod 10K+
P: 12,430
No problem twinny, glad to be of help.
Jul 2 '14 #12

NeoPa
Expert Mod 15k+
P: 31,769
Rabbit has provided illustrations that range from the most, to the absolute least, effect of using Domain Aggregate instead of Aggregate functions, and rightly indicates that even in such a rare case as when aggregating across the full domain for a domain which isn't otherwised reference within the SQL, it is still preferable to use the latter instead of the former.

His first example illustrates that the Domain Aggregate function is not equivalent to an Aggregate one where a GROUP BY is used (He has criteria in the DCount() that specifies which group of data within the domain to process through). Even if no GROUP BY is used (IE A single output record is required) then DCount() still ensures the domain is processed through twice instead of only once, and because it comes via a completely separate function call it cannot be included in any optimisations determined by pre-processing the SQL (Such as which indexes to use etc).

PS Twinnyfo - we prefer to think of ourselves as on the light side of The Force if you don't mind ;-) We'll leave the secrets of the dark side to others.
Jul 3 '14 #13

Post your reply

Sign in to post your reply or Sign up for a free account.