Data type mismatch (and IIF) quandry

[AUcane]

I am working on Access (XP).

I have a headcount table that has the following key fields:

Name
department
percent of time
start date

I am querying this table to determine a department's headcount per month. So, I have created 12 new fields (1-12, being the number of periods in a fiscal year) and the following equation for these fields:

1:IIF([ST DT]<8/1/2004,[PCT_TIME],"")
2:IIF([ST DT]<9/1/2004,[PCT_TIME],"")
etc.

Field "1" would give me the department's total headcount for July-04, "2" would give me the HC for Aug-04, etc. However, my result gives me blank cells for all - rather than the "PCT TIME" amount I'm looking for. I get the data type mismatch error msg when I put quotes on the dates (e.g. "8/1/2004").

Not sure how to fix - ideas?

Thanks!

 

[bat17]

use hash marks for dates #08/01/2004#

HTH

Peter

 

[AUcane]

hash marks worked - thanks! Actually, it also works if I changed the date format to a general/numerical format (i.e. 8/1/2004 = 38200).

Ok, now that it works, there's one more issue. This query gives me headcount by person by department for the 12 periods. My next step is to sum all of the headcounts in this first query so that I get total headcount by department for each period. However, with my IIF function (1: IIf([ST DT]<#8/1/2004#,[PCT_TIME],""), I beleive the [""] is actually causing there to be data in a cell so that when I COUNT the total headcount for a department with "blanks", I actually get headcount totals instead of zero. Is there something else I can use instead of the [""] (maybe some sort of ISBLANK formula) so that the cells with blanks don't actually get counted by the COUNT formula?

Thanks.

 

[bat17]

You could return a Null, but I am not sure if your data type will allow it. IIF([ST DT]<8/1/2004,[PCT_TIME],Null)

If you are trying to get a head count don't you need to sum rather than count anyway? otherwise you could use
IIF([ST DT]<8/1/2004,1,0) and count.


HTH

Peter