this is what i tried,
<cfquery name="EventResults"
datasource="#datasource#"
dbtype="ODBC">
SELECT ContestantID, SUM (AdjPoints) as TOTALPTS, SUM (Points) as PTS,
SUM(Convert(Float,TimeTaken)) as TOTALTIME, RodeoID, Place
FROM ResultsManager
WHERE EventID = #url.EventID# AND RodeoID = #url.RodeoID#
GROUP BY ContestantID, RodeoID, Place
ORDER BY Place
</cfquery>
the 'timetaken' field is a Number field (access for now) with a format of
"Fixed" and Decimal Places as '2'
<cfoutput>#NumberFormat(EventResults.TOTALTIME,"__.__")#</cfoutput>
and i get this error:
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'Convert' in
expression.
The error occurred while processing an element with a general identifier of
(CFQUERY), occupying document position (1:1) to (3:23).
any ideas?
thanks,
Jay Patton
Web Design / Application Design
Web Pro USA
406.549.3337 ext. 203
1.888.5WEBPRO
www.webpro-usa.com
----- Original Message -----
From: "Philip Arnold - ASP" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, February 19, 2001 12:43 PM
Subject: RE: SUM rounding
> > Can anyone help me on this. here is my querey and then the
> > output. it seems to be rounding the SUM (TimeTaken) as TotalTime
variable.
> > im tring to get it to output something like 12.03 or 03.98 ect....
> >
> > <cfquery name="EventResults"
> > datasource="#datasource#"
> > dbtype="ODBC">
> > SELECT ContestantID, SUM (AdjPoints) as TOTALPTS, SUM (Points) as
> > PTS, SUM (TimeTaken) as TotalTime, RodeoID, Place
> > FROM ResultsManager
> > WHERE EventID = #url.EventID# AND RodeoID = #url.RodeoID#
> > GROUP BY ContestantID, RodeoID, Place
> > ORDER BY Place
> > </cfquery>
> >
> > <cfoutput>#NumberFormat(EventResults.TotalTime,"___.__")#</cfoutput>
>
> SQL Server outputs aggrigate functions on the field type of the field,
thus
> SUM, AVG etc. will remain an INT if they are an INT
>
> If you want to have a FLOAT or NUMERIC as the return, then try;
> SELECT ContestantID, SUM(CONVERT(FLOAT,AdjPoints)) as TOTALPTS
>
> If the field isn't a numeric field type (i.e. DateTime or SmallDateTime)
> then this (probably) won't give a decent answer anyway
>
> Philip Arnold
> Director
> Certified ColdFusion Developer
> ASP Multimedia Limited
> T: +44 (0)20 8680 1133
>
> "Websites for the real world"
>
> **********************************************************************
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
> are addressed. If you have received this email in error please notify
> the system manager.
> **********************************************************************
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
