SUM in DRW

[Stephen Green]

I've create a Database Results chart that includes a column of numbers. Is
there a way I can have a total at the base of the column?

Thanks.

Stephen

PS the numbers are occasionally in the minus...

 

[Kathleen Anderson [MVP - FrontPage]]

Add a second DRW to your page, with the same criteria as the first, and
modify to add the SUM function, like this: SELECT SUM(yourAmount) AS yourSum
yourSum will show up on your page

 

[Stephen Green]

Kathleen!

Thanks. Can you remind me how to display a variable (in this case, yourSum)
in a table cell?

If I place it this way:

<td>yourSum</td>

it's treated as text.

Thanks again.

Stephen

 

[Kathleen Anderson [MVP - FrontPage]]

If you modify the SQL in the Custom Query box in the wizard it should
include that field when the page is generated.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

 

[Stephen Green]

It seems that the DRW doesn't like the query as it has a variable in it:

fp_sQry="SELECT ID, Approved, Description, TaskDate, HoursSubmitted FROM
Volunteer_Hours WHERE Volunteer_Hours.ID ="& vID

Thanks

Stephen

 

[Kathleen Anderson [MVP - FrontPage]]

Stephen:
How is this variable being passed to the query? Is it through an input text
box or hard-coded in your SQL?

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

It seems that the DRW doesn't like the query as it has a variable in
it:

fp_sQry="SELECT ID, Approved, Description, TaskDate, HoursSubmitted
FROM Volunteer_Hours WHERE Volunteer_Hours.ID ="& vID

Thanks

Stephen

If you modify the SQL in the Custom Query box in the wizard it should
include that field when the page is generated.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

Kathleen!

Thanks. Can you remind me how to display a variable (in this case,
yourSum) in a table cell?

If I place it this way:

<td>yourSum</td>

it's treated as text.

Thanks again.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message Add a second DRW to your page, with the same criteria as the first,
and modify to add the SUM function, like this: SELECT
SUM(yourAmount) AS yourSum yourSum will show up on your page

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



I've create a Database Results chart that includes a column of
numbers. Is there a way I can have a total at the base of the
column?

Thanks.

Stephen

PS the numbers are occasionally in the minus...

 

[Stephen Green]

On this particular page, I used the Spooky Diet - a macro that strips all of
the unnecessary code out of a Database Results page , making the page much
smaller. I can no longer use the DRW to make changes.

So I've been altering the SQL manually in HTML view.

Stephen

Stephen:
How is this variable being passed to the query? Is it through an input text
box or hard-coded in your SQL?

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

It seems that the DRW doesn't like the query as it has a variable in
it:

fp_sQry="SELECT ID, Approved, Description, TaskDate, HoursSubmitted
FROM Volunteer_Hours WHERE Volunteer_Hours.ID ="& vID

Thanks

Stephen

If you modify the SQL in the Custom Query box in the wizard it should
include that field when the page is generated.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



Kathleen!

Thanks. Can you remind me how to display a variable (in this case,
yourSum) in a table cell?

If I place it this way:

<td>yourSum</td>

it's treated as text.

Thanks again.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message Add a second DRW to your page, with the same criteria as the first,
and modify to add the SUM function, like this: SELECT
SUM(yourAmount) AS yourSum yourSum will show up on your page

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



I've create a Database Results chart that includes a column of
numbers. Is there a way I can have a total at the base of the
column?

Thanks.

Stephen

PS the numbers are occasionally in the minus...

 

[Kathleen Anderson [MVP - FrontPage]]

You can still use the wizard to insert a second DRW in the page, which is
where the summing would take place. That is still what you're asking about,
correct? Is the criteria for your search hard-coded in the SQL or is it
entered in a search form by the user?

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

On this particular page, I used the Spooky Diet - a macro that strips
all of the unnecessary code out of a Database Results page , making
the page much smaller. I can no longer use the DRW to make changes.

So I've been altering the SQL manually in HTML view.

Stephen

Stephen:
How is this variable being passed to the query? Is it through an
input text box or hard-coded in your SQL?

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

It seems that the DRW doesn't like the query as it has a variable in
it:

fp_sQry="SELECT ID, Approved, Description, TaskDate, HoursSubmitted
FROM Volunteer_Hours WHERE Volunteer_Hours.ID ="& vID

Thanks

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message If you modify the SQL in the Custom Query box in the wizard it
should include that field when the page is generated.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



Kathleen!

Thanks. Can you remind me how to display a variable (in this
case, yourSum) in a table cell?

If I place it this way:

<td>yourSum</td>

it's treated as text.

Thanks again.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message Add a second DRW to your page, with the same criteria as the
first, and modify to add the SUM function, like this: SELECT
SUM(yourAmount) AS yourSum yourSum will show up on your page

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



I've create a Database Results chart that includes a column of
numbers. Is there a way I can have a total at the base of the
column?

Thanks.

Stephen

PS the numbers are occasionally in the minus...

 

[Stephen Green]

Kathleen!

I was trying to add the code you suggested into an existing SQL statement
thus:

SELECT SUM(HoursSubmitted) AS totalHours FROM Volunteer_Hours WHERE
Volunteer_Hours.ID ='"'& vID

vID is a variable stored from an earlier DRW that accessed the members
information in another table.

The problem is that when I look at the finished code it looks like this:

<%=FP_FieldVal(fp_rs,"SUM(HoursSubmitted) AS totalHours") %>

This generates the following error:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name
or ordinal.

H:\HSHOME\SGREEN0\ANMT.ORG\MEMBERS\../_fpclass/fpdblib.inc, line 48

I figure this is because SUM(HoursSubmitted) AS totalHours is NOT a
fieldname in the table.

So I tried this:

<% totalHours %> - generates "Type mismatch: 'totalHours' "

<% Cstr(totalHours) %> - no error, but nothing is displayed

I'll keep trying, but for now I'm stuck.



Thanks for your help.

Stephen

You can still use the wizard to insert a second DRW in the page, which is
where the summing would take place. That is still what you're asking about,
correct? Is the criteria for your search hard-coded in the SQL or is it
entered in a search form by the user?

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

On this particular page, I used the Spooky Diet - a macro that strips
all of the unnecessary code out of a Database Results page , making
the page much smaller. I can no longer use the DRW to make changes.

So I've been altering the SQL manually in HTML view.

Stephen

Stephen:
How is this variable being passed to the query? Is it through an
input text box or hard-coded in your SQL?

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



It seems that the DRW doesn't like the query as it has a variable in
it:

fp_sQry="SELECT ID, Approved, Description, TaskDate, HoursSubmitted
FROM Volunteer_Hours WHERE Volunteer_Hours.ID ="& vID

Thanks

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message If you modify the SQL in the Custom Query box in the wizard it
should include that field when the page is generated.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



Kathleen!

Thanks. Can you remind me how to display a variable (in this
case, yourSum) in a table cell?

If I place it this way:

<td>yourSum</td>

it's treated as text.

Thanks again.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message Add a second DRW to your page, with the same criteria as the
first, and modify to add the SUM function, like this: SELECT
SUM(yourAmount) AS yourSum yourSum will show up on your page

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



I've create a Database Results chart that includes a column of
numbers. Is there a way I can have a total at the base of the
column?

Thanks.

Stephen

PS the numbers are occasionally in the minus...

 

[Kathleen Anderson [MVP - FrontPage]]

Stephen:
I wouldn't make the second DRW dependant on the first, especially if you've
put the first one on the Spooky Diet. Just create a second one on the page,
under the first, and use the same SQL as the first, except modify it to
include the SUM function. If you modify the SQL while you're running the
wizard, the totalHours field should show up on the page.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

Kathleen!

I was trying to add the code you suggested into an existing SQL
statement thus:

SELECT SUM(HoursSubmitted) AS totalHours FROM Volunteer_Hours WHERE
Volunteer_Hours.ID ='"'& vID

vID is a variable stored from an earlier DRW that accessed the members
information in another table.

The problem is that when I look at the finished code it looks like
this:

<%=FP_FieldVal(fp_rs,"SUM(HoursSubmitted) AS totalHours") %>

This generates the following error:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested
name or ordinal.

H:\HSHOME\SGREEN0\ANMT.ORG\MEMBERS\../_fpclass/fpdblib.inc, line 48

I figure this is because SUM(HoursSubmitted) AS totalHours is NOT a
fieldname in the table.

So I tried this:

<% totalHours %> - generates "Type mismatch: 'totalHours' "

<% Cstr(totalHours) %> - no error, but nothing is displayed

I'll keep trying, but for now I'm stuck.



Thanks for your help.

Stephen

You can still use the wizard to insert a second DRW in the page,
which is where the summing would take place. That is still what
you're asking about, correct? Is the criteria for your search
hard-coded in the SQL or is it entered in a search form by the user?

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

On this particular page, I used the Spooky Diet - a macro that
strips all of the unnecessary code out of a Database Results page ,
making the page much smaller. I can no longer use the DRW to make
changes.

So I've been altering the SQL manually in HTML view.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message Stephen:
How is this variable being passed to the query? Is it through an
input text box or hard-coded in your SQL?

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



It seems that the DRW doesn't like the query as it has a variable
in it:

fp_sQry="SELECT ID, Approved, Description, TaskDate,
HoursSubmitted FROM Volunteer_Hours WHERE Volunteer_Hours.ID ="&
vID

Thanks

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message If you modify the SQL in the Custom Query box in the wizard it
should include that field when the page is generated.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



Kathleen!

Thanks. Can you remind me how to display a variable (in this
case, yourSum) in a table cell?

If I place it this way:

<td>yourSum</td>

it's treated as text.

Thanks again.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message Add a second DRW to your page, with the same criteria as the
first, and modify to add the SUM function, like this: SELECT
SUM(yourAmount) AS yourSum yourSum will show up on your page

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



I've create a Database Results chart that includes a column of
numbers. Is there a way I can have a total at the base of the
column?

Thanks.

Stephen

PS the numbers are occasionally in the minus...

 

[Stephen Green]

Kathleen!

The code you suggested DOES work when I test it with a hard coded ID number.
But I have to use a variable. Here is the previous SQL statement that
works:

fp_sQry="SELECT ID, Approved, Description, TaskDate, HoursSubmitted FROM
Volunteer_Hours WHERE Volunteer_Hours.ID ="& vID

Notice where the quotes are.

I can't enter:

fp_sQry="SELECT SUM(HoursSubmitted) AS totalHours FROM Volunteer_Hours
WHERE Volunteer_Hours.ID ="& vID into the DRW.

No matter what I try I can't get the DRW to place the quotes properly for
the variable vID to be recognized as such. I'm sure that's the only issue,
because the above SQL produces correct results.

Thanks again.

Stephen

Stephen:
I wouldn't make the second DRW dependant on the first, especially if you've
put the first one on the Spooky Diet. Just create a second one on the page,
under the first, and use the same SQL as the first, except modify it to
include the SUM function. If you modify the SQL while you're running the
wizard, the totalHours field should show up on the page.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

Kathleen!

I was trying to add the code you suggested into an existing SQL
statement thus:

SELECT SUM(HoursSubmitted) AS totalHours FROM Volunteer_Hours WHERE
Volunteer_Hours.ID ='"'& vID

vID is a variable stored from an earlier DRW that accessed the members
information in another table.

The problem is that when I look at the finished code it looks like
this:

<%=FP_FieldVal(fp_rs,"SUM(HoursSubmitted) AS totalHours") %>

This generates the following error:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested
name or ordinal.

H:\HSHOME\SGREEN0\ANMT.ORG\MEMBERS\../_fpclass/fpdblib.inc, line 48

I figure this is because SUM(HoursSubmitted) AS totalHours is NOT a
fieldname in the table.

So I tried this:

<% totalHours %> - generates "Type mismatch: 'totalHours' "

<% Cstr(totalHours) %> - no error, but nothing is displayed

I'll keep trying, but for now I'm stuck.



Thanks for your help.

Stephen

You can still use the wizard to insert a second DRW in the page,
which is where the summing would take place. That is still what
you're asking about, correct? Is the criteria for your search
hard-coded in the SQL or is it entered in a search form by the user?

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



On this particular page, I used the Spooky Diet - a macro that
strips all of the unnecessary code out of a Database Results page ,
making the page much smaller. I can no longer use the DRW to make
changes.

So I've been altering the SQL manually in HTML view.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message Stephen:
How is this variable being passed to the query? Is it through an
input text box or hard-coded in your SQL?

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



It seems that the DRW doesn't like the query as it has a variable
in it:

fp_sQry="SELECT ID, Approved, Description, TaskDate,
HoursSubmitted FROM Volunteer_Hours WHERE Volunteer_Hours.ID ="&
vID

Thanks

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message If you modify the SQL in the Custom Query box in the wizard it
should include that field when the page is generated.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



Kathleen!

Thanks. Can you remind me how to display a variable (in this
case, yourSum) in a table cell?

If I place it this way:

<td>yourSum</td>

it's treated as text.

Thanks again.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message Add a second DRW to your page, with the same criteria as the
first, and modify to add the SUM function, like this: SELECT
SUM(yourAmount) AS yourSum yourSum will show up on your page

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



I've create a Database Results chart that includes a column of
numbers. Is there a way I can have a total at the base of the
column?

Thanks.

Stephen

PS the numbers are occasionally in the minus...

 

[Kathleen Anderson [MVP - FrontPage]]

Where does this variable come from? Is the user entering it into a search
box?

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

Kathleen!

The code you suggested DOES work when I test it with a hard coded ID
number. But I have to use a variable. Here is the previous SQL
statement that works:

fp_sQry="SELECT ID, Approved, Description, TaskDate, HoursSubmitted
FROM Volunteer_Hours WHERE Volunteer_Hours.ID ="& vID

Notice where the quotes are.

I can't enter:

fp_sQry="SELECT SUM(HoursSubmitted) AS totalHours FROM
Volunteer_Hours WHERE Volunteer_Hours.ID ="& vID into the DRW.

No matter what I try I can't get the DRW to place the quotes properly
for the variable vID to be recognized as such. I'm sure that's the
only issue, because the above SQL produces correct results.

Thanks again.

Stephen

Stephen:
I wouldn't make the second DRW dependant on the first, especially if
you've put the first one on the Spooky Diet. Just create a second
one on the page, under the first, and use the same SQL as the first,
except modify it to include the SUM function. If you modify the SQL
while you're running the wizard, the totalHours field should show up
on the page.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

Kathleen!

I was trying to add the code you suggested into an existing SQL
statement thus:

SELECT SUM(HoursSubmitted) AS totalHours FROM Volunteer_Hours
WHERE Volunteer_Hours.ID ='"'& vID

vID is a variable stored from an earlier DRW that accessed the
members information in another table.

The problem is that when I look at the finished code it looks like
this:

<%=FP_FieldVal(fp_rs,"SUM(HoursSubmitted) AS totalHours") %>

This generates the following error:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the
requested name or ordinal.

H:\HSHOME\SGREEN0\ANMT.ORG\MEMBERS\../_fpclass/fpdblib.inc, line 48

I figure this is because SUM(HoursSubmitted) AS totalHours is NOT a
fieldname in the table.

So I tried this:

<% totalHours %> - generates "Type mismatch: 'totalHours' "

<% Cstr(totalHours) %> - no error, but nothing is displayed

I'll keep trying, but for now I'm stuck.



Thanks for your help.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message You can still use the wizard to insert a second DRW in the page,
which is where the summing would take place. That is still what
you're asking about, correct? Is the criteria for your search
hard-coded in the SQL or is it entered in a search form by the
user?

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



On this particular page, I used the Spooky Diet - a macro that
strips all of the unnecessary code out of a Database Results page
, making the page much smaller. I can no longer use the DRW to
make changes.

So I've been altering the SQL manually in HTML view.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message Stephen:
How is this variable being passed to the query? Is it through an
input text box or hard-coded in your SQL?

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



It seems that the DRW doesn't like the query as it has a
variable in it:

fp_sQry="SELECT ID, Approved, Description, TaskDate,
HoursSubmitted FROM Volunteer_Hours WHERE Volunteer_Hours.ID ="&
vID

Thanks

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message If you modify the SQL in the Custom Query box in the wizard it
should include that field when the page is generated.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



Kathleen!

Thanks. Can you remind me how to display a variable (in this
case, yourSum) in a table cell?

If I place it this way:

<td>yourSum</td>

it's treated as text.

Thanks again.

Stephen

"Kathleen Anderson [MVP - FrontPage]"
Add a second DRW to your page, with the same criteria as the
first, and modify to add the SUM function, like this: SELECT
SUM(yourAmount) AS yourSum yourSum will show up on your page

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



I've create a Database Results chart that includes a column
of numbers. Is there a way I can have a total at the base
of the column?

Thanks.

Stephen

PS the numbers are occasionally in the minus...

 

[Stephen Green]

No, it is saved earlier on in the page. I created variables in an earlier
DRW that queries a different table (members):

<% dim vID
vID = FP_FieldVal(fp_rs,"ID") %>
<% Session("uID") = FP_FieldVal(fp_rs,"ID") %>

I tried using both vID and <%=Session("uID")%>, but neither works.

I do use <%=Session("uID)%> later in the page (in a form field) and it works
fine.

It may help if you look at the page yourself. There are three DRW's and a
form to collect data and post it to the table. The problem is in the third
table - I have currently hard-coded an ID into it so it works. To look at
the page got to
http://www.anmt.org/temp/ANMT_request_for_Volunteer_Credit1.asp and use the
Username edostal and the Password sing.

The page grabs the name from the login program and goes from there. You
will notice that I placed an ID field in the form at the bottom of the page
to test for the session variable.

Thanks

Stephen

Where does this variable come from? Is the user entering it into a search
box?

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

Kathleen!

The code you suggested DOES work when I test it with a hard coded ID
number. But I have to use a variable. Here is the previous SQL
statement that works:

fp_sQry="SELECT ID, Approved, Description, TaskDate, HoursSubmitted
FROM Volunteer_Hours WHERE Volunteer_Hours.ID ="& vID

Notice where the quotes are.

I can't enter:

fp_sQry="SELECT SUM(HoursSubmitted) AS totalHours FROM
Volunteer_Hours WHERE Volunteer_Hours.ID ="& vID into the DRW.

No matter what I try I can't get the DRW to place the quotes properly
for the variable vID to be recognized as such. I'm sure that's the
only issue, because the above SQL produces correct results.

Thanks again.

Stephen

Stephen:
I wouldn't make the second DRW dependant on the first, especially if
you've put the first one on the Spooky Diet. Just create a second
one on the page, under the first, and use the same SQL as the first,
except modify it to include the SUM function. If you modify the SQL
while you're running the wizard, the totalHours field should show up
on the page.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



Kathleen!

I was trying to add the code you suggested into an existing SQL
statement thus:

SELECT SUM(HoursSubmitted) AS totalHours FROM Volunteer_Hours
WHERE Volunteer_Hours.ID ='"'& vID

vID is a variable stored from an earlier DRW that accessed the
members information in another table.

The problem is that when I look at the finished code it looks like
this:

<%=FP_FieldVal(fp_rs,"SUM(HoursSubmitted) AS totalHours") %>

This generates the following error:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the
requested name or ordinal.

H:\HSHOME\SGREEN0\ANMT.ORG\MEMBERS\../_fpclass/fpdblib.inc, line 48

I figure this is because SUM(HoursSubmitted) AS totalHours is NOT a
fieldname in the table.

So I tried this:

<% totalHours %> - generates "Type mismatch: 'totalHours' "

<% Cstr(totalHours) %> - no error, but nothing is displayed

I'll keep trying, but for now I'm stuck.



Thanks for your help.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message You can still use the wizard to insert a second DRW in the page,
which is where the summing would take place. That is still what
you're asking about, correct? Is the criteria for your search
hard-coded in the SQL or is it entered in a search form by the
user?

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



On this particular page, I used the Spooky Diet - a macro that
strips all of the unnecessary code out of a Database Results page
, making the page much smaller. I can no longer use the DRW to
make changes.

So I've been altering the SQL manually in HTML view.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message Stephen:
How is this variable being passed to the query? Is it through an
input text box or hard-coded in your SQL?

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



It seems that the DRW doesn't like the query as it has a
variable in it:

fp_sQry="SELECT ID, Approved, Description, TaskDate,
HoursSubmitted FROM Volunteer_Hours WHERE Volunteer_Hours.ID ="&
vID

Thanks

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message If you modify the SQL in the Custom Query box in the wizard it
should include that field when the page is generated.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



Kathleen!

Thanks. Can you remind me how to display a variable (in this
case, yourSum) in a table cell?

If I place it this way:

<td>yourSum</td>

it's treated as text.

Thanks again.

Stephen

"Kathleen Anderson [MVP - FrontPage]"
Add a second DRW to your page, with the same criteria as the
first, and modify to add the SUM function, like this: SELECT
SUM(yourAmount) AS yourSum yourSum will show up on your page

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



I've create a Database Results chart that includes a column
of numbers. Is there a way I can have a total at the base
of the column?

Thanks.

Stephen

PS the numbers are occasionally in the minus...

 

[Kathleen Anderson [MVP - FrontPage]]

Stephen:

Setting the format of the variables aside, let me give you an example of
what I'm saying.

Go to this page:
http://www.osc.state.ct.us/finance/reports/rptmuni20022003.asp This page
actually consists of two DRW regions; the first is the detail for Ansonia
through Woodbury; the second is the Total line.

The SQL for the first DRW looks like this:

SELECT Municipality,Format(SUM(Allocation_Amount),'$#,##0.00') AS
DollarField2,Format(SUM(How_Much),'$#,##0.00') AS DollarField5 FROM ""Bond
Commission Data Input"" WHERE (Municipality IS NOT NULL) AND (Date BETWEEN
#07/01/2002# AND #06/30/2003#) GROUP BY Municipality ORDER BY Municipality

The SQL for the second DRW looks like this:

SELECT Format(SUM(Allocation_Amount),'$#,##0.00') AS
DollarField2,Format(SUM(How_Much),'$#,##0.00') AS DollarField5 FROM ""Bond
Commission Data Input"" WHERE (Municipality IS NOT NULL) AND (Date BETWEEN
#07/01/2002# AND #06/30/2003#)

You don't need to pass anything from the first DRW to the second DRW, just
use the same criteria in both queries.



--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

No, it is saved earlier on in the page. I created variables in an
earlier DRW that queries a different table (members):

<% dim vID
vID = FP_FieldVal(fp_rs,"ID") %>
<% Session("uID") = FP_FieldVal(fp_rs,"ID") %>

I tried using both vID and <%=Session("uID")%>, but neither works.

I do use <%=Session("uID)%> later in the page (in a form field) and
it works fine.

It may help if you look at the page yourself. There are three DRW's
and a form to collect data and post it to the table. The problem is
in the third table - I have currently hard-coded an ID into it so it
works. To look at the page got to
http://www.anmt.org/temp/ANMT_request_for_Volunteer_Credit1.asp and
use the Username edostal and the Password sing.

The page grabs the name from the login program and goes from there.
You will notice that I placed an ID field in the form at the bottom
of the page to test for the session variable.

Thanks

Stephen

Where does this variable come from? Is the user entering it into a
search box?

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

Kathleen!

The code you suggested DOES work when I test it with a hard coded ID
number. But I have to use a variable. Here is the previous SQL
statement that works:

fp_sQry="SELECT ID, Approved, Description, TaskDate, HoursSubmitted
FROM Volunteer_Hours WHERE Volunteer_Hours.ID ="& vID

Notice where the quotes are.

I can't enter:

fp_sQry="SELECT SUM(HoursSubmitted) AS totalHours FROM
Volunteer_Hours WHERE Volunteer_Hours.ID ="& vID into the DRW.

No matter what I try I can't get the DRW to place the quotes
properly for the variable vID to be recognized as such. I'm sure
that's the only issue, because the above SQL produces correct
results.

Thanks again.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message Stephen:
I wouldn't make the second DRW dependant on the first, especially
if you've put the first one on the Spooky Diet. Just create a
second one on the page, under the first, and use the same SQL as
the first, except modify it to include the SUM function. If you
modify the SQL while you're running the wizard, the totalHours
field should show up on the page.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



Kathleen!

I was trying to add the code you suggested into an existing SQL
statement thus:

SELECT SUM(HoursSubmitted) AS totalHours FROM Volunteer_Hours
WHERE Volunteer_Hours.ID ='"'& vID

vID is a variable stored from an earlier DRW that accessed the
members information in another table.

The problem is that when I look at the finished code it looks like
this:

<%=FP_FieldVal(fp_rs,"SUM(HoursSubmitted) AS totalHours") %>

This generates the following error:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the
requested name or ordinal.

H:\HSHOME\SGREEN0\ANMT.ORG\MEMBERS\../_fpclass/fpdblib.inc, line
48

I figure this is because SUM(HoursSubmitted) AS totalHours is NOT
a fieldname in the table.

So I tried this:

<% totalHours %> - generates "Type mismatch: 'totalHours' "

<% Cstr(totalHours) %> - no error, but nothing is displayed

I'll keep trying, but for now I'm stuck.



Thanks for your help.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message You can still use the wizard to insert a second DRW in the page,
which is where the summing would take place. That is still what
you're asking about, correct? Is the criteria for your search
hard-coded in the SQL or is it entered in a search form by the
user?

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



On this particular page, I used the Spooky Diet - a macro that
strips all of the unnecessary code out of a Database Results
page , making the page much smaller. I can no longer use the
DRW to make changes.

So I've been altering the SQL manually in HTML view.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message Stephen:
How is this variable being passed to the query? Is it through
an input text box or hard-coded in your SQL?

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



It seems that the DRW doesn't like the query as it has a
variable in it:

fp_sQry="SELECT ID, Approved, Description, TaskDate,
HoursSubmitted FROM Volunteer_Hours WHERE Volunteer_Hours.ID
="& vID

Thanks

Stephen

"Kathleen Anderson [MVP - FrontPage]"
If you modify the SQL in the Custom Query box in the wizard
it should include that field when the page is generated.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



Kathleen!

Thanks. Can you remind me how to display a variable (in
this case, yourSum) in a table cell?

If I place it this way:

<td>yourSum</td>

it's treated as text.

Thanks again.

Stephen

"Kathleen Anderson [MVP - FrontPage]"
Add a second DRW to your page, with the same criteria as
the first, and modify to add the SUM function, like this:
SELECT SUM(yourAmount) AS yourSum yourSum will show up on
your page

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



I've create a Database Results chart that includes a
column of numbers. Is there a way I can have a total at
the base of the column?

Thanks.

Stephen

PS the numbers are occasionally in the minus...

 

[Stephen Green]

Kathleen!

Thanks for the samples. I'm sorry to be dumb about this, but...

Until the form at the bottom, this page operates without user intervention.
I get the user's name from the login. I use that to customize the header.

The only way I can get the user's ID is to use the name from the login to go
to the members table and extract the data. That's when I save the ID to a
variable because it is the link between the various tables. This DRW
displays the User's Name Task Assignment and Level.

The Next DRW goes to a different table and displays the Volunteer Hours
submitted and approved/disapproved.

The third is the Total Hours we are discussing that won't work so far with
out hard-coding the user's ID.

Then there is and input form for the user to enter Volunteer Hours for
credit.

Did you look at
http://www.anmt.org/temp/ANMT_request_for_Volunteer_Credit1.asp
(edostal/sing)?



The second DRW

Stephen:

Setting the format of the variables aside, let me give you an example of
what I'm saying.

Go to this page:
http://www.osc.state.ct.us/finance/reports/rptmuni20022003.asp This page
actually consists of two DRW regions; the first is the detail for Ansonia
through Woodbury; the second is the Total line.

The SQL for the first DRW looks like this:

SELECT Municipality,Format(SUM(Allocation_Amount),'$#,##0.00') AS
DollarField2,Format(SUM(How_Much),'$#,##0.00') AS DollarField5 FROM ""Bond
Commission Data Input"" WHERE (Municipality IS NOT NULL) AND (Date BETWEEN
#07/01/2002# AND #06/30/2003#) GROUP BY Municipality ORDER BY Municipality

The SQL for the second DRW looks like this:

SELECT Format(SUM(Allocation_Amount),'$#,##0.00') AS
DollarField2,Format(SUM(How_Much),'$#,##0.00') AS DollarField5 FROM ""Bond
Commission Data Input"" WHERE (Municipality IS NOT NULL) AND (Date BETWEEN
#07/01/2002# AND #06/30/2003#)

You don't need to pass anything from the first DRW to the second DRW, just
use the same criteria in both queries.



--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

No, it is saved earlier on in the page. I created variables in an
earlier DRW that queries a different table (members):

<% dim vID
vID = FP_FieldVal(fp_rs,"ID") %>
<% Session("uID") = FP_FieldVal(fp_rs,"ID") %>

I tried using both vID and <%=Session("uID")%>, but neither works.

I do use <%=Session("uID)%> later in the page (in a form field) and
it works fine.

It may help if you look at the page yourself. There are three DRW's
and a form to collect data and post it to the table. The problem is
in the third table - I have currently hard-coded an ID into it so it
works. To look at the page got to
http://www.anmt.org/temp/ANMT_request_for_Volunteer_Credit1.asp and
use the Username edostal and the Password sing.

The page grabs the name from the login program and goes from there.
You will notice that I placed an ID field in the form at the bottom
of the page to test for the session variable.

Thanks

Stephen

Where does this variable come from? Is the user entering it into a
search box?

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



Kathleen!

The code you suggested DOES work when I test it with a hard coded ID
number. But I have to use a variable. Here is the previous SQL
statement that works:

fp_sQry="SELECT ID, Approved, Description, TaskDate, HoursSubmitted
FROM Volunteer_Hours WHERE Volunteer_Hours.ID ="& vID

Notice where the quotes are.

I can't enter:

fp_sQry="SELECT SUM(HoursSubmitted) AS totalHours FROM
Volunteer_Hours WHERE Volunteer_Hours.ID ="& vID into the DRW.

No matter what I try I can't get the DRW to place the quotes
properly for the variable vID to be recognized as such. I'm sure
that's the only issue, because the above SQL produces correct
results.

Thanks again.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message Stephen:
I wouldn't make the second DRW dependant on the first, especially
if you've put the first one on the Spooky Diet. Just create a
second one on the page, under the first, and use the same SQL as
the first, except modify it to include the SUM function. If you
modify the SQL while you're running the wizard, the totalHours
field should show up on the page.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



Kathleen!

I was trying to add the code you suggested into an existing SQL
statement thus:

SELECT SUM(HoursSubmitted) AS totalHours FROM Volunteer_Hours
WHERE Volunteer_Hours.ID ='"'& vID

vID is a variable stored from an earlier DRW that accessed the
members information in another table.

The problem is that when I look at the finished code it looks like
this:

<%=FP_FieldVal(fp_rs,"SUM(HoursSubmitted) AS totalHours") %>

This generates the following error:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the
requested name or ordinal.

H:\HSHOME\SGREEN0\ANMT.ORG\MEMBERS\../_fpclass/fpdblib.inc, line
48

I figure this is because SUM(HoursSubmitted) AS totalHours is NOT
a fieldname in the table.

So I tried this:

<% totalHours %> - generates "Type mismatch: 'totalHours' "

<% Cstr(totalHours) %> - no error, but nothing is displayed

I'll keep trying, but for now I'm stuck.



Thanks for your help.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message You can still use the wizard to insert a second DRW in the page,
which is where the summing would take place. That is still what
you're asking about, correct? Is the criteria for your search
hard-coded in the SQL or is it entered in a search form by the
user?

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



On this particular page, I used the Spooky Diet - a macro that
strips all of the unnecessary code out of a Database Results
page , making the page much smaller. I can no longer use the
DRW to make changes.

So I've been altering the SQL manually in HTML view.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message Stephen:
How is this variable being passed to the query? Is it through
an input text box or hard-coded in your SQL?

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



It seems that the DRW doesn't like the query as it has a
variable in it:

fp_sQry="SELECT ID, Approved, Description, TaskDate,
HoursSubmitted FROM Volunteer_Hours WHERE Volunteer_Hours.ID
="& vID

Thanks

Stephen

"Kathleen Anderson [MVP - FrontPage]"
If you modify the SQL in the Custom Query box in the wizard
it should include that field when the page is generated.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



Kathleen!

Thanks. Can you remind me how to display a variable (in
this case, yourSum) in a table cell?

If I place it this way:

<td>yourSum</td>

it's treated as text.

Thanks again.

Stephen

"Kathleen Anderson [MVP - FrontPage]"
Add a second DRW to your page, with the same criteria as
the first, and modify to add the SUM function, like this:
SELECT SUM(yourAmount) AS yourSum yourSum will show up on
your page

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



I've create a Database Results chart that includes a
column of numbers. Is there a way I can have a total at
the base of the column?

Thanks.

Stephen

PS the numbers are occasionally in the minus...

 

[Stephen Green]

Kathleen!

I got it working. The solution (for me) was to insert the DRW, then run
Spooky's Diet. I could then edit the SQL without the DRW's strictures.
This is the SQL that worked:

fp_sQry = "SELECT SUM(HoursSubmitted) AS totalHours FROM Volunteer_Hours
WHERE Volunteer_Hours.ID ="& session("uID")

The DRW wouldn't allow for that punctuation...

Thanks for all your help.

Stephen

Kathleen!

Thanks for the samples. I'm sorry to be dumb about this, but...

Until the form at the bottom, this page operates without user intervention.
I get the user's name from the login. I use that to customize the header.

The only way I can get the user's ID is to use the name from the login to go
to the members table and extract the data. That's when I save the ID to a
variable because it is the link between the various tables. This DRW
displays the User's Name Task Assignment and Level.

The Next DRW goes to a different table and displays the Volunteer Hours
submitted and approved/disapproved.

The third is the Total Hours we are discussing that won't work so far with
out hard-coding the user's ID.

Then there is and input form for the user to enter Volunteer Hours for
credit.

Did you look at
http://www.anmt.org/temp/ANMT_request_for_Volunteer_Credit1.asp
(edostal/sing)?



The second DRW

Stephen:

Setting the format of the variables aside, let me give you an example of
what I'm saying.

Go to this page:
http://www.osc.state.ct.us/finance/reports/rptmuni20022003.asp This page
actually consists of two DRW regions; the first is the detail for Ansonia
through Woodbury; the second is the Total line.

The SQL for the first DRW looks like this:

SELECT Municipality,Format(SUM(Allocation_Amount),'$#,##0.00') AS
DollarField2,Format(SUM(How_Much),'$#,##0.00') AS DollarField5 FROM ""Bond
Commission Data Input"" WHERE (Municipality IS NOT NULL) AND (Date BETWEEN
#07/01/2002# AND #06/30/2003#) GROUP BY Municipality ORDER BY Municipality

The SQL for the second DRW looks like this:

SELECT Format(SUM(Allocation_Amount),'$#,##0.00') AS
DollarField2,Format(SUM(How_Much),'$#,##0.00') AS DollarField5 FROM ""Bond
Commission Data Input"" WHERE (Municipality IS NOT NULL) AND (Date BETWEEN
#07/01/2002# AND #06/30/2003#)

You don't need to pass anything from the first DRW to the second DRW, just
use the same criteria in both queries.



--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

No, it is saved earlier on in the page. I created variables in an
earlier DRW that queries a different table (members):

<% dim vID
vID = FP_FieldVal(fp_rs,"ID") %>
<% Session("uID") = FP_FieldVal(fp_rs,"ID") %>

I tried using both vID and <%=Session("uID")%>, but neither works.

I do use <%=Session("uID)%> later in the page (in a form field) and
it works fine.

It may help if you look at the page yourself. There are three DRW's
and a form to collect data and post it to the table. The problem is
in the third table - I have currently hard-coded an ID into it so it
works. To look at the page got to
http://www.anmt.org/temp/ANMT_request_for_Volunteer_Credit1.asp and
use the Username edostal and the Password sing.

The page grabs the name from the login program and goes from there.
You will notice that I placed an ID field in the form at the bottom
of the page to test for the session variable.

Thanks

Stephen

in message Where does this variable come from? Is the user entering it into a
search box?

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



Kathleen!

The code you suggested DOES work when I test it with a hard coded ID
number. But I have to use a variable. Here is the previous SQL
statement that works:

fp_sQry="SELECT ID, Approved, Description, TaskDate, HoursSubmitted
FROM Volunteer_Hours WHERE Volunteer_Hours.ID ="& vID

Notice where the quotes are.

I can't enter:

fp_sQry="SELECT SUM(HoursSubmitted) AS totalHours FROM
Volunteer_Hours WHERE Volunteer_Hours.ID ="& vID into the DRW.

No matter what I try I can't get the DRW to place the quotes
properly for the variable vID to be recognized as such. I'm sure
that's the only issue, because the above SQL produces correct
results.

Thanks again.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message Stephen:
I wouldn't make the second DRW dependant on the first, especially
if you've put the first one on the Spooky Diet. Just create a
second one on the page, under the first, and use the same SQL as
the first, except modify it to include the SUM function. If you
modify the SQL while you're running the wizard, the totalHours
field should show up on the page.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



Kathleen!

I was trying to add the code you suggested into an existing SQL
statement thus:

SELECT SUM(HoursSubmitted) AS totalHours FROM Volunteer_Hours
WHERE Volunteer_Hours.ID ='"'& vID

vID is a variable stored from an earlier DRW that accessed the
members information in another table.

The problem is that when I look at the finished code it looks like
this:

<%=FP_FieldVal(fp_rs,"SUM(HoursSubmitted) AS totalHours") %>

This generates the following error:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the
requested name or ordinal.

H:\HSHOME\SGREEN0\ANMT.ORG\MEMBERS\../_fpclass/fpdblib.inc, line
48

I figure this is because SUM(HoursSubmitted) AS totalHours is NOT
a fieldname in the table.

So I tried this:

<% totalHours %> - generates "Type mismatch: 'totalHours' "

<% Cstr(totalHours) %> - no error, but nothing is displayed

I'll keep trying, but for now I'm stuck.



Thanks for your help.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message You can still use the wizard to insert a second DRW in the page,
which is where the summing would take place. That is still what
you're asking about, correct? Is the criteria for your search
hard-coded in the SQL or is it entered in a search form by the
user?

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



On this particular page, I used the Spooky Diet - a macro that
strips all of the unnecessary code out of a Database Results
page , making the page much smaller. I can no longer use the
DRW to make changes.

So I've been altering the SQL manually in HTML view.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message Stephen:
How is this variable being passed to the query? Is it through
an input text box or hard-coded in your SQL?

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



It seems that the DRW doesn't like the query as it has a
variable in it:

fp_sQry="SELECT ID, Approved, Description, TaskDate,
HoursSubmitted FROM Volunteer_Hours WHERE Volunteer_Hours.ID
="& vID

Thanks

Stephen

"Kathleen Anderson [MVP - FrontPage]"
If you modify the SQL in the Custom Query box in the wizard
it should include that field when the page is generated.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



Kathleen!

Thanks. Can you remind me how to display a variable (in
this case, yourSum) in a table cell?

If I place it this way:

<td>yourSum</td>

it's treated as text.

Thanks again.

Stephen

"Kathleen Anderson [MVP - FrontPage]"
Add a second DRW to your page, with the same criteria as
the first, and modify to add the SUM function, like this:
SELECT SUM(yourAmount) AS yourSum yourSum will show up on
your page

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



I've create a Database Results chart that includes a
column of numbers. Is there a way I can have a total at
the base of the column?

Thanks.

Stephen

PS the numbers are occasionally in the minus...

 

[Kathleen Anderson [MVP - FrontPage]]

That's great! I've always been meaning to try Spooky's diet; glad to know it
worked for you.

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

Kathleen!

I got it working. The solution (for me) was to insert the DRW, then
run Spooky's Diet. I could then edit the SQL without the DRW's
strictures. This is the SQL that worked:

fp_sQry = "SELECT SUM(HoursSubmitted) AS totalHours FROM
Volunteer_Hours WHERE Volunteer_Hours.ID ="& session("uID")

The DRW wouldn't allow for that punctuation...

Thanks for all your help.

Stephen

Kathleen!

Thanks for the samples. I'm sorry to be dumb about this, but...

Until the form at the bottom, this page operates without user
intervention. I get the user's name from the login. I use that to
customize the header.

The only way I can get the user's ID is to use the name from the
login to go to the members table and extract the data. That's when
I save the ID to a variable because it is the link between the
various tables. This DRW displays the User's Name Task Assignment
and Level.

The Next DRW goes to a different table and displays the Volunteer
Hours submitted and approved/disapproved.

The third is the Total Hours we are discussing that won't work so
far with out hard-coding the user's ID.

Then there is and input form for the user to enter Volunteer Hours
for credit.

Did you look at
http://www.anmt.org/temp/ANMT_request_for_Volunteer_Credit1.asp
(edostal/sing)?



The second DRW

Stephen:

Setting the format of the variables aside, let me give you an
example of what I'm saying.

Go to this page:
http://www.osc.state.ct.us/finance/reports/rptmuni20022003.asp
This page actually consists of two DRW regions; the first is the
detail for Ansonia through Woodbury; the second is the Total line.

The SQL for the first DRW looks like this:

SELECT Municipality,Format(SUM(Allocation_Amount),'$#,##0.00') AS
DollarField2,Format(SUM(How_Much),'$#,##0.00') AS DollarField5 FROM
""Bond Commission Data Input"" WHERE (Municipality IS NOT NULL) AND
(Date BETWEEN #07/01/2002# AND #06/30/2003#) GROUP BY Municipality
ORDER BY Municipality

The SQL for the second DRW looks like this:

SELECT Format(SUM(Allocation_Amount),'$#,##0.00') AS
DollarField2,Format(SUM(How_Much),'$#,##0.00') AS DollarField5 FROM
""Bond Commission Data Input"" WHERE (Municipality IS NOT NULL) AND
(Date BETWEEN #07/01/2002# AND #06/30/2003#)

You don't need to pass anything from the first DRW to the second
DRW, just use the same criteria in both queries.



--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



No, it is saved earlier on in the page. I created variables in an
earlier DRW that queries a different table (members):

<% dim vID
vID = FP_FieldVal(fp_rs,"ID") %>
<% Session("uID") = FP_FieldVal(fp_rs,"ID") %>

I tried using both vID and <%=Session("uID")%>, but neither works.

I do use <%=Session("uID)%> later in the page (in a form field) and
it works fine.

It may help if you look at the page yourself. There are three
DRW's and a form to collect data and post it to the table. The
problem is in the third table - I have currently hard-coded an ID
into it so it works. To look at the page got to
http://www.anmt.org/temp/ANMT_request_for_Volunteer_Credit1.asp and
use the Username edostal and the Password sing.

The page grabs the name from the login program and goes from there.
You will notice that I placed an ID field in the form at the bottom
of the page to test for the session variable.

Thanks

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message Where does this variable come from? Is the user entering it into a
search box?

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



Kathleen!

The code you suggested DOES work when I test it with a hard
coded ID number. But I have to use a variable. Here is the
previous SQL statement that works:

fp_sQry="SELECT ID, Approved, Description, TaskDate,
HoursSubmitted FROM Volunteer_Hours WHERE Volunteer_Hours.ID ="&
vID

Notice where the quotes are.

I can't enter:

fp_sQry="SELECT SUM(HoursSubmitted) AS totalHours FROM
Volunteer_Hours WHERE Volunteer_Hours.ID ="& vID into the DRW.

No matter what I try I can't get the DRW to place the quotes
properly for the variable vID to be recognized as such. I'm sure
that's the only issue, because the above SQL produces correct
results.

Thanks again.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message Stephen:
I wouldn't make the second DRW dependant on the first,
especially if you've put the first one on the Spooky Diet. Just
create a second one on the page, under the first, and use the
same SQL as the first, except modify it to include the SUM
function. If you modify the SQL while you're running the
wizard, the totalHours field should show up on the page.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



Kathleen!

I was trying to add the code you suggested into an existing SQL
statement thus:

SELECT SUM(HoursSubmitted) AS totalHours FROM Volunteer_Hours
WHERE Volunteer_Hours.ID ='"'& vID

vID is a variable stored from an earlier DRW that accessed the
members information in another table.

The problem is that when I look at the finished code it looks
like this:

<%=FP_FieldVal(fp_rs,"SUM(HoursSubmitted) AS totalHours") %>

This generates the following error:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the
requested name or ordinal.

H:\HSHOME\SGREEN0\ANMT.ORG\MEMBERS\../_fpclass/fpdblib.inc,
line 48

I figure this is because SUM(HoursSubmitted) AS totalHours is
NOT a fieldname in the table.

So I tried this:

<% totalHours %> - generates "Type mismatch: 'totalHours' "

<% Cstr(totalHours) %> - no error, but nothing is displayed

I'll keep trying, but for now I'm stuck.



Thanks for your help.

Stephen

"Kathleen Anderson [MVP - FrontPage]" <[email protected]>
wrote in message You can still use the wizard to insert a second DRW in the
page, which is where the summing would take place. That is
still what you're asking about, correct? Is the criteria for
your search hard-coded in the SQL or is it entered in a
search form by the user?

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



On this particular page, I used the Spooky Diet - a macro
that strips all of the unnecessary code out of a Database
Results page , making the page much smaller. I can no
longer use the DRW to make changes.

So I've been altering the SQL manually in HTML view.

Stephen

"Kathleen Anderson [MVP - FrontPage]"
Stephen:
How is this variable being passed to the query? Is it
through an input text box or hard-coded in your SQL?

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



It seems that the DRW doesn't like the query as it has a
variable in it:

fp_sQry="SELECT ID, Approved, Description, TaskDate,
HoursSubmitted FROM Volunteer_Hours WHERE
Volunteer_Hours.ID ="& vID

Thanks

Stephen

"Kathleen Anderson [MVP - FrontPage]"
If you modify the SQL in the Custom Query box in the
wizard it should include that field when the page is
generated.

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx



Kathleen!

Thanks. Can you remind me how to display a variable (in
this case, yourSum) in a table cell?

If I place it this way:

<td>yourSum</td>

it's treated as text.

Thanks again.

Stephen

"Kathleen Anderson [MVP - FrontPage]"
Add a second DRW to your page, with the same criteria as
the first, and modify to add the SUM function, like
this: SELECT SUM(yourAmount) AS yourSum yourSum will
show up on your page

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



I've create a Database Results chart that includes a
column of numbers. Is there a way I can have a total
at the base of the column?

Thanks.

Stephen

PS the numbers are occasionally in the minus...