Problems using Count or Sum of IIF, then function

[jabrony76]

Hi all,
I have been attempting to figure out how to work Sum of an IIF function for work. Here's the Scenario:
I have Time of Referral and Time of Contact fields in a table. I have the following to calculate the time difference in minutes.

Expr1: DateDiff("n",[Date/Time of Referral],[Date/Time of Contact])

Next I have the following to code each record

Expr2: IIf([Expr1]<120,&quot;1&quot;,&quot;0&quot

Above is looking to code the contacts that were seen within 120 minutes (2 hours)with a 1 and those over with a 0.

After that I have another Query that asks for the Count of All Contacts, and then I am attempting to do:

Expr3: Sum [Expr2]

and then I would simply perform

[Expr3]/[Count of Contacts]

to get the compliant percentage. Maybe the long way to do it but its the only way i could figure out.

Unfortunately, Access (97 by the way) is stating that MS Jet DB engine cannot execute the SQL statement because it contains a field that has an invalid data type when I attempt to calculate the Sum of Expr2. I don't understand this. Expr2 is either a 1 or a 0 and it won't let me calculate a sum! Any Ideas? Thoughts on how this can be fixed or be done differently? I'm not very good with code so please be gentle.
Thanks,
Andy

 

[tlbroadbent]

Remove the &quot; around 1 and 0 in the IIF statement. Putting &quot; around them causes Access to treat them as text data which it cannot sum. Terry

http://members.home.net/tlbroadbent/index.htm

 

[MichaelRed]

Expr3: Su[red]([/red][Expr2][red])[/red]

Also add the parens shown here in red.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over