Hi folks, this is a bit OT, but I thought there might be a better way to do
what's not working in CF anyway, so here it is:
I have the following code:
<cfset week = #now()# - #createtimespan(7, 0, 0, 0)#>
<cfset month = #now()# - #createtimespan(30, 0, 0, 0)#>
<cfset year = #now()# - #createtimespan(365, 0, 0, 0)#>
<cfquery name="pullcount" datasource="#dbvar#">
SELECT SUM(totalbirds) AS weekbirds,
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
AND site.site_date between #now()# AND #createodbcdate(month)#
GROUP BY site.per_id, person.fname, person.lname
</cfquery>
It works fine, but what I want to do is pull the bird counts for the week,
the month and the year. I think I should be able to do it with something
like:
<cfquery name="pullcount" datasource="#dbvar#">
SELECT IIF(site.site_date between #now()# AND #createodbcdate(month)#,
SUM(totalbirds) AS monthbirds, 0),
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
AND GROUP BY site.per_id, person.fname, person.lname
</cfquery>
But, what I get is this lovely error:
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression 'IIF(site.site_date between #5/4/2000 14:04:35# AND
#4/4/2000#, SUM(totalbirds) AS monthbirds, 0)'.
Help?
TIA,
Deanna
************************************************************
Deanna Schneider
Interactive Media Developer
UWEX Cooperative Extension Electronic Publishing Group
103 Extension Bldg
432 N. Lake Street
Madison, WI 53706
(608) 265-7923
------------------------------------------------------------------------------
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.
