Using DLookUp and Count in Expression Builder

[haydenal]

I am trying to count the number of occurrences of particular values for a specific year. I was thinking of trying something such as:

2008: Count([TYPE]) WHERE [YEAR] = 2008

Obviously, WHERE isn't available to me. I've been trying to do this with DLookUp but I just can't seem to get it right. I'm using:

2008: Count(DLookUp("[TYPE]","TABLE","[YEAR] = 2008"))

This seems to get me close but when I try to do it with 2007, the values returned are the same as 2008.

Anyone have any tips on doing this?

 

[boblarson]

Try this instead

2008:Sum(IIf([Year]=2008,1,0))

I really would not use reserved words as your object/field names (Year is an Access Reserved word) as it can screw things up for you royally.

Check out this reference for reserved words:
http://www.allenbrowne.com/AppIssueBadWord.html

 

[haydenal]

Thanks Bob,

That works perfectly.

Andy