Same problem. In this line of your query |FROM ResultsManager INNER JOIN ContestantManager ON |ResultsManager.ContestantID = ListGetAt(ContestantManager.ContestantID, |"ListFind(ContestantManager.ContestantID, |"#ResultsManager.ContestantID#")") You're referencing a variable #ResultsManager.ContestantID# The only way you could have a variable of that name is if 1) You'd created a structure ccalled ResultsManager with a key called ContestantID and you were referencing that key value 2) You'd run a query called ResultsManager that pulled a column called ContestantID Since neither one of these happened, you're causing an error when you run the ListFind function in your Inner Join. And I wholly agree with Philip on the normalisation issue (not that it matters). Do a lookup table..... Much better form it is. |-----Original Message----- |From: Jay Patton [mailto:[EMAIL PROTECTED]] |Sent: Thursday, March 01, 2001 1:32 PM |To: Patricia Lee; [EMAIL PROTECTED] |Subject: Re: !!!Statement Question.!!! (whole page) | | |here is the whole CF page. i hope this helps. | |<!-- Query For Women ONLY --> | |<cfquery name="GetTotalPts" | datasource="#datasource#" | dbtype="ODBC" | username="#username#" | password="#password#"> |SELECT ResultsManager.ContestantID, ContestantManager.FirstName, |ContestantManager.LastName, ContestantManager.City, |ContestantManager.State, |SUM (ResultsManager.AdjPoints) as TOTALPTS |FROM ResultsManager INNER JOIN ContestantManager ON |ResultsManager.ContestantID = ListGetAt(ContestantManager.ContestantID, |"ListFind(ContestantManager.ContestantID, |"#ResultsManager.ContestantID#")") |WHERE ContestantManager.Gender = '2' AND |ResultsManager.NotAllAround = '0' |GROUP BY ResultsManager.ContestantID, ContestantManager.FirstName, |ContestantManager.LastName, ContestantManager.City, |ContestantManager.State |ORDER BY SUM (AdjPoints) DESC |</cfquery> | | | |<!--- <cfset Place = GetTotalPts.RecordCount> ---> |<cfset count = 1> |<cfset NOW = #Now()#> | |<html> |<head> |<cfinclude template="styles.cfm"> |<title>NSPRA</title> |</head> | |<body bgcolor="#FFFFFF"> |<table width="600" border="0" cellspacing="2" cellpadding="2"> |<tr> | <td colspan="4" bgcolor="#FFFFFF"><font |face="verdana,arial,helvetica" |size="2">All Around by Age Group:</font></td> | </tr> | <tr> | <td colspan="4" bgcolor="#FFFFFF"><select |onChange="window.parent.location.href=this.options[this.selecte |dIndex].value |" name="select"> | <option>Select an Age Group | <option>-------------------- | <option value="OverallWomensResults.cfm">All Around |Results (All Women) | <option> ---------------- | <option>By Age Group | <option> ---------------- | <option value="Womens4050.cfm">Womens 40-50 | <option value="Womens5060.cfm">Womens 50-60 | <option value="Womens60Plus.cfm">Womens 60+ | </td> | </tr> | <tr> | <td colspan="4"><font face="Verdana, Arial, Helvetica, sans-serif" |size="2"><b>All Around: Women</b> <cfoutput><font size="1">(as of |#DateFormat(Now,"mm.dd.yyyy")# #TimeFormat(Now,"hh:mmtt")# |ET)</font></cfoutput></font></td> | </tr> | <tr> | <td width="6" bgcolor="#CCCCCC"><font face="Verdana, |Arial, Helvetica, |sans-serif" size="1"><b>Place</b></font></td> | <td width="251" bgcolor="#CCCCCC"><font face="Verdana, |Arial, Helvetica, |sans-serif" size="1"><b>Name</b></font></td> | <td width="85" bgcolor="#CCCCCC"><font face="Verdana, |Arial, Helvetica, |sans-serif" size="1"><b>Adj. Points</b></font></td> | </tr> | <CFPARAM NAME="URL.StartRow" DEFAULT="1"> | <cfoutput query="GetTotalPts" startrow=#URL.StartRow# maxrows=25> | |<tr bgcolor="#IIf(GetTotalPts.CurrentRow Mod 2, DE('FFFFE7'), |DE('99CC99'))#"> | <td width="6"><font face="verdana,arial,helvetica" |size="1"><center>#currentRow#</center></font></td> | <td width="251"><font face="Verdana, Arial, Helvetica, sans-serif" |size="1">#GetTotalPts.LastName#, #GetTotalPts.FirstName# <font |size="1">(#GetTotalPts.City#, #GetTotalPts.State#)</font></font></td> | <td width="85"><font face="Verdana, Arial, Helvetica, sans-serif" |size="1">#GetTotalPts.TOTALPTS#</font></td> | </tr> | </cfoutput> | | <tr> | <td width="6" bgcolor="#CCCCCC"><font face="Verdana, |Arial, Helvetica, |sans-serif" size="1"><b>Place</b></font></td> | <td width="251" bgcolor="#CCCCCC"><font face="Verdana, |Arial, Helvetica, |sans-serif" size="1"><b>Name</b></font></td> | <td width="85" bgcolor="#CCCCCC"><font face="Verdana, |Arial, Helvetica, |sans-serif" size="1"><b>Adj. Points</b></font></td> | </tr> | <tr bgcolor="#FFFFFF"> | <td colspan="4"><font face="verdana,arial,helvetica" |size="1"><CFOUTPUT><cfif url.startRow NEQ 1><a |href="javascript:history.go(-1)"><b>Back</b></a> &nb |sp;| &nb |sp; </cfif><cfif url.startRow + 25 LT GetTotalPts.recordCount><A |HREF="OverallWomensResults.cfm?StartRow=#Evaluate(URL.StartRow + |25)#"><b>Next 25</b></a></cfif></CFOUTPUT></font></td> | </tr> |</table> |<cfinclude template="footer.cfm"> |</body> |</html> | |Jay Patton |Web Design / Application Design |Web Pro USA |406.549.3337 ext. 203 |1.888.5WEBPRO |www.webpro-usa.com |----- Original Message ----- |From: "Patricia Lee" <[EMAIL PROTECTED]> |To: <[EMAIL PROTECTED]> |Cc: <[EMAIL PROTECTED]> |Sent: Thursday, March 01, 2001 11:26 AM |Subject: RE: !!!Statement Question.!!! | | |> Where is the variable Resultsmanager.contestantID supposed |to be coming |> from? You're problem is that it doesn't exist. It just ain't there. |> |> Is this supposed to be a query that ran before the gettotalpts query? |> <shrug> impossible to tell. |> |> You didn't include any of the CF, but I can guess from the |error that this |> might be *one* thing you did wrong. Why? |> |> | |> |An error occurred while evaluating the expression: |> | |> | |> |#ResultsManager.ContestantID# |> |> Unless there is a CF variable called |resultsmanager.contestantid somewhere |> on this page it is wholly predictable that this error will |happen. You're |> trying to output a variable that doesn't exist. |> |> |> |-----Original Message----- |> |From: Jay Patton [mailto:[EMAIL PROTECTED]] |> |Sent: Thursday, March 01, 2001 12:53 PM |> |To: CF-Talk |> |Subject: !!!Statement Question.!!! |> | |> | |> |This list has been good to me so far so i thought i would ask |> |another question. |> | |> |Could anyone help me with this? ive been going over and over |> |it for a while now and cant seem to get it figured out. what i |> |have is a ResultsManager and in that i have a ContestantID |> |that is pulled from the ContestantManager. this was working |> |fine untill my client said that there may be times where |> |contestants tie in certain places so i changed the |> |ContestantID in the resultsmanager to nvarchar as the data |> |type so that it could take input like... 6,161 (contestantid 6 |> |and contestantid 161) now on the output i had to change the |> |statement to the following but we get an error: (included at |> |bottom) what i would like to know is am i going about this the |> |proper way or do i need to redo my statement? ANY and ALL Help |> |will be MUCH MUCH apreciated. (please reply to on list and CC: |> |to me directly as well, it seems sometimes that i dont get my |> |list mail as often as i should. but i would like for others to |> |maybe learn from this as well:) |> | |> |<cfquery name="GetTotalPts" |> | datasource="#datasource#" |> | dbtype="ODBC" |> | username="#username#" |> | password="#password#"> |> |SELECT ResultsManager.ContestantID, |> |ContestantManager.FirstName, ContestantManager.LastName, |> |ContestantManager.City, ContestantManager.State, SUM |> |(ResultsManager.AdjPoints) as TOTALPTS |> |FROM ResultsManager INNER JOIN ContestantManager ON |> |ResultsManager.ContestantID = |> |ListGetAt(ContestantManager.ContestantID, |> |"ListFind(ContestantManager.ContestantID, |> |"#ResultsManager.ContestantID#")") |> |WHERE ContestantManager.Gender = '2' AND |> |ResultsManager.NotAllAround = '0' |> |GROUP BY ResultsManager.ContestantID, |> |ContestantManager.FirstName, ContestantManager.LastName, |> |ContestantManager.City, ContestantManager.State |> |ORDER BY SUM (AdjPoints) DESC |> |</cfquery> |> | |> |ERROR: |> |Error Diagnostic Information |> | |> |An error occurred while evaluating the expression: |> | |> | |> |#ResultsManager.ContestantID# |> | |> | |> | |> |Error near line 9, column 169. |> | |> |Thanks, |> | |> |Jay Patton |> |Web Design / Application Design |> |Web Pro USA |> |406.549.3337 ext. 203 |> |1.888.5WEBPRO |> |www.webpro-usa.com |> | |> | |> | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 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
