I think you've got the SUM and IIF nested the wrony way 'round - try
SUM(IIF(site_date < {d '2000-04-27'}, totalbirds, 0))
> -----Original Message-----
> From: Deanna L. Schneider [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, May 04, 2000 10:07 PM
> To: [EMAIL PROTECTED]
> Subject: Re: OT - IIF function in Access
>
>
> Thanks, John, but that's not really the problem, I don't think.
>
> The query works if I use constants like this.
>
>
> <cfquery name="pullcount" datasource="#dbvar#">
> SELECT IIF(1>0, sum(totalbirds), 0) AS weekbirds,
> IIF(1>0, sum(totalbirds), 0) AS monthbirds,
> IIF(1>0, sum(totalbirds), 0)AS yearbirds,
> site.per_id,
> person.fname,
> person.lname
> FROM sighting, site, person
> WHERE sighting.site_id = site.site_id
> AND site.per_id = person.per_id
> GROUP BY site.per_id, person.fname, person.lname
> </cfquery>
>
>
> But if I try to throw a variable in there....it doesn't work.
> More thoughts?
> -d
>
>
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error
> (missing operator) in
> query expression 'IIF(site_date < #4/27/2000# sum(totalbirds), 0)'.
>
>
> SQL = "SELECT IIF(site_date < {d '2000-04-27'} sum(totalbirds), 0) AS
> weekbirds, IIF(1>0, sum(totalbirds), 0) AS monthbirds, IIF(1>0,
> sum(totalbirds), 0)AS yearbirds, site.per_id, person.fname,
> person.lname
> FROM sighting, site, person WHERE sighting.site_id = site.site_id AND
> site.per_id = person.per_id GROUP BY site.per_id, person.fname,
> person.lname"
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
