Syntax error in
http://www.ncsaa.org/stat_leaders_board.asp in line 408
If PassAttempts < 1 Then PassAttempts = 1&><%=FormatNumber........
Should be
If PassAttempts < 1 Then PassAttempts = 1 THEN %><%=FormatNumber........
PS
Your ORDER BY is also Invalid
ORDER BY (Saves / (Saves + Goals_Allowed)) DESC
- you can not do math in an order by
(plus if it did work the eval can give you a div by 0 error, or just a # which can't be compared to anything in the DB)
IF you really need that type of sort, AND it can Never evaluate to 0) create the variable as an alias
SELECT (Saves / (Saves + Goals_Allowed)) AS SavesPCT, XX FROM Results WHERE (Goals_Allowed +
Saves ORDER BY SavesPCT
- but you can't use a wildcard * in the SELECT * FROM
- for XX in the above you need to list each field value individually as
SELECT (Saves / (Saves + Goals_Allowed)) AS SavesPCT, SavesPCT, Saves, Goal_Allowed, etc
And that type of query is very inefficient because it is constantly reordering all the data w/ each computation
You are much better off saving the value of (Saves / (Saves + Goals_Allowed)) as a db field (SavesPCT) you can then refer to
directly in the future any time you update Goals_Allowed or Saves
-
--
_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
_____________________________________________
| It's not the percent formatting that's causing the problem; it's the
| calculation itself that is not working.
|
|
|
| "David Berry" wrote:
|
| > Format(myVariable,'##0.00##%') as newVariable
| >
| >
| > | > > Trevor, it's been working well -- I've got a number of calculations
| > > working
| > > correctly now; I've been using variables to "check" to be sure that some
| > > fields (by which I want to divide) do not return a "zero" value and cause
| > > a
| > > problem. So I've been using a variable to check that it's not a zero, and
| > > if
| > > it is, I've changed the variable to a value of 1 (one).
| > >
| > > HOWEVER...I have this one pesky calculation that just is NOT working.
| > > I've
| > > copied my code (below), if you can take a look...and you can also check
| > > this
| > > page out live at
http://www.ncsaa.org/stat_leaders_board.asp
| > >
| > > (It's the calculation of Save Percentage, using the variables TotalSaves,
| > > TotalGoals, and ShotsFaced, that is causing me problems.)
| > >
| > > Thanks...Any help would be great!
| > >
| > >
| > > -- Nate
| > > ---------------------------------
| > >
| > > </table>
| > > <p align="center" style="margin-top: 0; margin-bottom: 0">
| > > </p>
| > > <p align="center" style="margin-top: 0; margin-bottom: 0">
| > > <font size="4" color="#0000FF"><b>SAVE PERCENTAGE
| > > (KEEPER)</b></font></p>
| > > <p align="center" style="margin-top: 0; margin-bottom: 0">
| > > <font size="2" color="#0000FF"><b>(Minimum 60 shots
| > > faced)</b></font></p>
| > > <p align="center" style="margin-top: 0; margin-bottom: 0"> <table
| > > width="100%" border="1">
| > > <thead>
| > > <tr>
| > > <th><b>Last Name</b></th>
| > > <th><b>First Name</b></th>
| > > <th><b>Grade</b></th>
| > > <th><b>School</b></th>
| > > <th><b>Saves</b></th>
| > > <th><b>Goals Allowed</b></th>
| > > <th>Save Percentage</th>
| > > </tr>
| > > </thead>
| > > <tbody>
| > > <!--webbot bot="DatabaseRegionStart"
| > >
s-columnnames="ID,School,HS_Enrollment,Last_Name,First_Name,Grade,Games_Played,Shots_At_Goal,Goals_Scored,Assists,Goals_Allowed,Saves,Shutouts,Remote_computer_name,User_name,Browser_type,Timestamp"
| > > s-columntypes="3,202,3,202,202,202,3,3,3,3,3,3,3,202,202,202,135"
| > > s-dataconnection="stat_submission_form_soccer_boys" b-tableformat="TRUE"
| > > b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE"
| > > b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE"
| > > b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource
| > > s-displaycolumns="Last_Name,First_Name,Grade,School,Saves,Goals_Allowed"
| > > s-criteria s-order s-sql="SELECT * FROM Results WHERE (Goals_Allowed +
| > > Saves
| > >> 60) ORDER BY (Saves / (Saves + Goals_Allowed)) DESC" b-procedure="FALSE"
| > > clientside suggestedext="asp" s-defaultfields s-norecordsfound="No records
| > > returned." i-maxrecords="20" i-groupsize="0" botid="20"
| > > u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc"
| > > u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY" preview="<tr><td colspan=64
| > > bgcolor="#FFFF00" width="100%"><font color="#000000">This is the start of
| > > a
| > > Database Results region. The page must be fetched from a web server with a
| > > web browser to display correctly; the current web is stored on your local
| > > disk or network.</font></td></tr>" startspan --><!--#include
| > > file="_fpclass/fpdblib.inc"-->
| > > <% if 0 then %>
| > > <SCRIPT Language="JavaScript">
| > > document.write("<div style='background: yellow; color: black;'>The
| > > Database
| > > Results component on this page is unable to display database content. The
| > > page must have a filename ending in '.asp', and the web must be hosted on
| > > a
| > > server that supports Active Server Pages.</div>");
| > > </SCRIPT>
| > > <% end if %>
| > > <%
| > > fp_sQry="SELECT * FROM Results WHERE (Goals_Allowed + Saves > 60) ORDER
| > > BY
| > > (Saves / (Saves + Goals_Allowed)) DESC"
| > > fp_sDefault=""
| > > fp_sNoRecords="<tr><td colspan=6 align=""LEFT"" width=""100%"">No records
| > > returned.</td></tr>"
| > > fp_sDataConn="stat_submission_form_soccer_boys"
| > > fp_iMaxRecords=20
| > > fp_iCommandType=1
| > > fp_iPageSize=0
| > > fp_fTableFormat=True
| > > fp_fMenuFormat=False
| > > fp_sMenuChoice=""
| > > fp_sMenuValue=""
| > >
fp_sColTypes="&ID=3&School=202&HS_Enrollment=3&Last_Name=202&First_Name=202&Grade=202&Games_Played=3&Shots_At_Goal=3&Goals_Scored=3&Assists=3&Goals_Allowed=3&Saves=3&Shutouts=3&Remote_computer_name=202&User_name=202&Browser_type=202&Timestamp=135&"
| > > fp_iDisplayCols=6
| > > fp_fCustomQuery=True
| > > BOTID=20
| > > fp_iRegion=BOTID
| > > %>
| > > <!--#include file="_fpclass/fpdbrgn1.inc"-->
| > > <!--webbot bot="DatabaseRegionStart" endspan i-checksum="40941" --><tr>
| > > <td align="center"><font size="2">
| > > <!--webbot bot="DatabaseResultColumn"
| > >
s-columnnames="ID,School,HS_Enrollment,Last_Name,First_Name,Grade,Games_Played,Shots_At_Goal,Goals_Scored,Assists,Goals_Allowed,Saves,Shutouts,Remote_computer_name,User_name,Browser_type,Timestamp"
| > > s-column="Last_Name" b-tableformat="TRUE" b-hashtml="FALSE"
| > > b-makelink="FALSE" clientside b-MenuFormat preview="<font
| > > size="-1"><<</font>Last_Name<font size="-1">>></font>"
| > > startspan
| > > --><%=FP_FieldVal(fp_rs,"Last_Name")%><!--webbot
| > > bot="DatabaseResultColumn"
| > > endspan i-checksum="17991" --></font></td>
| > > <td align="center"><font size="2">
| > > <!--webbot bot="DatabaseResultColumn"
| > >
s-columnnames="ID,School,HS_Enrollment,Last_Name,First_Name,Grade,Games_Played,Shots_At_Goal,Goals_Scored,Assists,Goals_Allowed,Saves,Shutouts,Remote_computer_name,User_name,Browser_type,Timestamp"
| > > s-column="First_Name" b-tableformat="TRUE" b-hashtml="FALSE"
| > > b-makelink="FALSE" clientside b-MenuFormat preview="<font
| > > size="-1"><<</font>First_Name<font size="-1">>></font>"
| > > startspan
| > > --><%=FP_FieldVal(fp_rs,"First_Name")%><!--webbot
| > > bot="DatabaseResultColumn"
| > > endspan i-checksum="27645" --></font></td>
| > > <td align="center"><font size="2">
| > > <!--webbot bot="DatabaseResultColumn"
| > >
s-columnnames="ID,School,HS_Enrollment,Last_Name,First_Name,Grade,Games_Played,Shots_At_Goal,Goals_Scored,Assists,Goals_Allowed,Saves,Shutouts,Remote_computer_name,User_name,Browser_type,Timestamp"
| > > s-column="Grade" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE"
| > > clientside b-MenuFormat preview="<font size="-1"><<</font>Grade<font
| > > size="-1">>></font>" startspan
| > > --><%=FP_FieldVal(fp_rs,"Grade")%><!--webbot bot="DatabaseResultColumn"
| > > endspan i-checksum="9058" --></font></td>
| > > <td align="center"><font size="2">
| > > <!--webbot bot="DatabaseResultColumn"
| > >
s-columnnames="ID,School,HS_Enrollment,Last_Name,First_Name,Grade,Games_Played,Shots_At_Goal,Goals_Scored,Assists,Goals_Allowed,Saves,Shutouts,Remote_computer_name,User_name,Browser_type,Timestamp"
| > > s-column="School" b-tableformat="TRUE" b-hashtml="FALSE"
| > > b-makelink="FALSE"
| > > clientside b-MenuFormat preview="<font
| > > size="-1"><<</font>School<font
| > > size="-1">>></font>" startspan
| > > --><%=FP_FieldVal(fp_rs,"School")%><!--webbot bot="DatabaseResultColumn"
| > > endspan i-checksum="14919" --></font></td>
| > > <td align="center"><font size="2">
| > > <!--webbot bot="DatabaseResultColumn"
| > >
s-columnnames="ID,School,HS_Enrollment,Last_Name,First_Name,Grade,Games_Played,Shots_At_Goal,Goals_Scored,Assists,Goals_Allowed,Saves,Shutouts,Remote_computer_name,User_name,Browser_type,Timestamp"
| > > s-column="Saves" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE"
| > > clientside b-MenuFormat preview="<font size="-1"><<</font>Saves<font
| > > size="-1">>></font>" startspan
| > > --><%=FP_FieldVal(fp_rs,"Saves")%><!--webbot bot="DatabaseResultColumn"
| > > endspan i-checksum="7729" --></font></td>
| > > <td align="center"><font size="2">
| > > <!--webbot bot="DatabaseResultColumn"
| > >
s-columnnames="ID,School,HS_Enrollment,Last_Name,First_Name,Grade,Games_Played,Shots_At_Goal,Goals_Scored,Assists,Goals_Allowed,Saves,Shutouts,Remote_computer_name,User_name,Browser_type,Timestamp"
| > > s-column="Goals_Allowed" b-tableformat="TRUE" b-hashtml="FALSE"
| > > b-makelink="FALSE" clientside b-MenuFormat preview="<font
| > > size="-1"><<</font>Goals_Allowed<font size="-1">>></font>"
| > > startspan --><%=FP_FieldVal(fp_rs,"Goals_Allowed")%><!--webbot
| > > bot="DatabaseResultColumn" endspan i-checksum="35843" --></font></td>
| > > <td align="center"><font
| > > size="2">
| > >
<%TotalGoals=FP_FieldVal(fp_rs,"Goals_Allowed")%><%TotalSaves=FP_FieldVal(fp_rs,"Saves")%><%ShotsFaced=TotalGoals+TotalSaves%><%If
| > > ShotsFaced < 1 Then ShotsFaced = 1%><%=TotalSaves /
| > > ShotsFaced%></font></td>
| > > </tr>
| > > <!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE"
| > > b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="0"
| > > clientside tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00"
| > > width="100%"><font color="#000000">This is the end of a Database Results
| > > region.</font></td></tr>" startspan --><!--#include
| > > file="_fpclass/fpdbrgn2.inc"-->
| > > <!--webbot bot="DatabaseRegionEnd" endspan i-checksum="62730" --></tbody>
| > >
| > >
| > >
| > >
| > >
| > > "Trevor L." wrote:
| > >
| > >> montiy wrote:
| > >> > OK, here's my situation...
| > >> >
| > >> > I have imported data from a database into a DRW (Database Results
| > >> > Wizard) area. Then I used some of that data to perform a
| > >> > calculation. (I did this by inserting the calculation code into an
| > >> > empty cell in a table.) This is my code (dividing one field by
| > >> > another):
| > >> >
| > >> > <%=(FP_FieldVal(fp_rs,"Goals_Scored")/FP_FieldVal(fp_rs,"Shots_At_Goal"))*100%>
| > >> >
| > >> > At this point I have some numbers with long numbers of decimal places
| > >> > (such as 83.3333333333333).
| > >> >
| > >> > NOW I need some additional help (and hopefully this is simple). I
| > >> > need to know how to modify my code in order to display the
| > >> > calculation as a PERCENT, rounded to two decimal places. Can someone
| > >> > help with this?
| > >> >
| > >> >
| > >> > -- Nate
| > >>
| > >> Weell, my answer to the previous post is below
| > >>
| > >> This last post of yours answers one question - you are getting the
| > >> percentage you want, just too many decimal places
| > >> So try:
| > >> <%= FormatNumber( FP_FieldVal(fp_rs,"Goals_Scored") * 100 /
| > >> FP_FieldVal(fp_rs,"Shots_At_Goal") , 2) & "%" %>
| > >>
| > >> and see what happens
| > >>
| > >> Remember I am no ASP/VBScript expert but this seems reasonable
| > >> (I got the FormatNumber function from
| > >>
http://www.w3schools.com/vbscript/func_formatnumber.asp )
| > >>
| > >> MY PREVIOUS POST
| > >> ================
| > >> Hi NAte,
| > >>
| > >> I sent some code to do the calculations before the data was entered, but
| > >> another poster said you wanted ASP, so I desisted from further posting,
| > >> easpcailly asI don't know much ASP
| > >>
| > >> Is your code not working ? It certainly looks like a percentage
| > >> calculation.
| > >> I would try:
| > >> <%= FP_FieldVal(fp_rs,"Goals_Scored") /
| > >> FP_FieldVal(fp_rs,"Shots_At_Goal") * 100 %>
| > >> Or
| > >>
| > >> <%= FP_FieldVal(fp_rs,"Goals_Scored") * 100 /
| > >> FP_FieldVal(fp_rs,"Shots_At_Goal") %>
| > >>
| > >> To display the % sign, I would try
| > >> <%= FP_FieldVal(fp_rs,"Goals_Scored") * 100 /
| > >> FP_FieldVal(fp_rs,"Shots_At_Goal") & "%" %>
| > >>
| > >> But to round it, try
| > >> <%= FormatNumber( FP_FieldVal(fp_rs,"Goals_Scored") * 100 /
| > >> FP_FieldVal(fp_rs,"Shots_At_Goal") , 2) & "%" %>
| > >>
| > >> I have not tested any of this
| > >>
| > >> --
| > >> Cheers,
| > >> Trevor L.
