I have an Access database used to track donor pledges. In it, there is a
table that contains three fields for each donor: Gift_Amount, Gift_Per_Year,
and Matching_Gift_Ratio.
The following formula would calculate the total pledge amount for each
donor:
(Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio + 1).
A total Pledge for all donors would just sum up the calculated values.
My goal is to create an ASP that would show this total amount pledged. Here
is the ASP that I have created so far:
***********************************************
<html>
<head>
<title>Pledge Totals</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsGiving 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database
Dim totalamt 'Holds the Total Pledge amount, a calculated field
Dim dollars 'Holds the formated currency amount
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less
connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Careathon_SP2004.mdb")
'Set an active connection to the Connection object using DSN connection
'adoCon.Open "Careathon_SP2004"
'Create an ADO recordset object
Set rsGiving = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT Giving.Gift_Amount, Giving.Gifts_Per_Year,
Giving.Matching_Gift_Ratio FROM Giving WHERE Gift_Amount>0 AND
Gifts_Per_Year>0"
'Open the recordset with the SQL query
rsGiving.Open strSQL, adoCon
'Loop through the recordset
Do While not rsGiving.EOF
'Calculate total donor pledge and add to cumulative total - THIS IS WHERE
THE CODE STOPS AND THE ERROR OCCURS
totalamt = totalamt + ((rsGiving("Gift_Amount") *
rsGiving("Gifts_Per_Year")) * (rsGiving("Matching_Gift_Ratio") + 1))
'Move to the next record in the recordset
rsGiving.MoveNext
Loop
'Formats the total amount to currency
'set dollars = FormatCurrency(totalamt, 2, -1, 0, 1)
'Write the HTML to display the total pledge amount
Response.Write ("<br> $ ")
'Response.Write (dollars)
Response.Write (totalamt)
Response.Write ("<br>")
'Reset server objects
rsGiving.Close
Set rsGiving = Nothing
Set adoCon = Nothing
Set totalamt = Nothing
Set dollars = Nothing
%>
</body>
</html>
**********************************************
However, when I run this page, I get the following error:
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch
It occurs on the line with the calculations.
What do I need to change in order for this page to work correctly?
13  2361 
Why not:
SELECT DonorID, ((Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio + 1))
FROM TableName
AND
SELECT SUM(GiftAmount) FROM TableName
Run these two queries and you do not have to calculate in ASP.
NOTE: I generally develop against SQL Server (MSDE is also acceptable) or
Oracle, not Access, so you may have to tweak the SQL.
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
************************************************** ********************
Think Outside the Box!
************************************************** ********************
<tp******@kc.rr.com> wrote in message
news:Qx*******************@twister.rdc-kc.rr.com... I have an Access database used to track donor pledges. In it, there is a
table that contains three fields for each donor: Gift_Amount,
Gift_Per_Year, and Matching_Gift_Ratio.
The following formula would calculate the total pledge amount for each
donor:
(Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio + 1).
A total Pledge for all donors would just sum up the calculated values.
My goal is to create an ASP that would show this total amount pledged.
Here is the ASP that I have created so far:
***********************************************
<html>
<head>
<title>Pledge Totals</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsGiving 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database
Dim totalamt 'Holds the Total Pledge amount, a calculated field
Dim dollars 'Holds the formated currency amount
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less
connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Careathon_SP2004.mdb")
'Set an active connection to the Connection object using DSN connection
'adoCon.Open "Careathon_SP2004"
'Create an ADO recordset object
Set rsGiving = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the
database strSQL = "SELECT Giving.Gift_Amount, Giving.Gifts_Per_Year,
Giving.Matching_Gift_Ratio FROM Giving WHERE Gift_Amount>0 AND
Gifts_Per_Year>0"
'Open the recordset with the SQL query
rsGiving.Open strSQL, adoCon
'Loop through the recordset
Do While not rsGiving.EOF
'Calculate total donor pledge and add to cumulative total - THIS IS WHERE
THE CODE STOPS AND THE ERROR OCCURS
totalamt = totalamt + ((rsGiving("Gift_Amount") *
rsGiving("Gifts_Per_Year")) * (rsGiving("Matching_Gift_Ratio") + 1))
'Move to the next record in the recordset
rsGiving.MoveNext
Loop
'Formats the total amount to currency
'set dollars = FormatCurrency(totalamt, 2, -1, 0, 1)
'Write the HTML to display the total pledge amount
Response.Write ("<br> $ ")
'Response.Write (dollars)
Response.Write (totalamt)
Response.Write ("<br>")
'Reset server objects
rsGiving.Close
Set rsGiving = Nothing
Set adoCon = Nothing
Set totalamt = Nothing
Set dollars = Nothing
%>
</body>
</html>
**********************************************
However, when I run this page, I get the following error:
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch
It occurs on the line with the calculations.
What do I need to change in order for this page to work correctly?
What I need is the first query to calculate (Gift_Amount * Gift_Per_Year) *
(Matching_Gift_Ratio + 1)) for each donor, and then a second query to sum
the results of the first query.
"Cowboy (Gregory A. Beamer)" <No************@comcast.netNoSpamM> wrote in
message news:eV**************@tk2msftngp13.phx.gbl... Why not:
SELECT DonorID, ((Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio +
1)) FROM TableName
AND
SELECT SUM(GiftAmount) FROM TableName
Run these two queries and you do not have to calculate in ASP.
NOTE: I generally develop against SQL Server (MSDE is also acceptable) or
Oracle, not Access, so you may have to tweak the SQL.
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
************************************************** ********************
Think Outside the Box!
************************************************** ********************
<tp******@kc.rr.com> wrote in message
news:Qx*******************@twister.rdc-kc.rr.com... I have an Access database used to track donor pledges. In it, there is
a table that contains three fields for each donor: Gift_Amount,
Gift_Per_Year, and Matching_Gift_Ratio.
The following formula would calculate the total pledge amount for each
donor:
(Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio + 1).
A total Pledge for all donors would just sum up the calculated values.
My goal is to create an ASP that would show this total amount pledged.
Here is the ASP that I have created so far:
***********************************************
<html>
<head>
<title>Pledge Totals</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsGiving 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database
Dim totalamt 'Holds the Total Pledge amount, a calculated field
Dim dollars 'Holds the formated currency amount
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less
connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Careathon_SP2004.mdb")
'Set an active connection to the Connection object using DSN
connection 'adoCon.Open "Careathon_SP2004"
'Create an ADO recordset object
Set rsGiving = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the
database strSQL = "SELECT Giving.Gift_Amount, Giving.Gifts_Per_Year,
Giving.Matching_Gift_Ratio FROM Giving WHERE Gift_Amount>0 AND
Gifts_Per_Year>0"
'Open the recordset with the SQL query
rsGiving.Open strSQL, adoCon
'Loop through the recordset
Do While not rsGiving.EOF
'Calculate total donor pledge and add to cumulative total - THIS IS
WHERE THE CODE STOPS AND THE ERROR OCCURS
totalamt = totalamt + ((rsGiving("Gift_Amount") *
rsGiving("Gifts_Per_Year")) * (rsGiving("Matching_Gift_Ratio") + 1))
'Move to the next record in the recordset
rsGiving.MoveNext
Loop
'Formats the total amount to currency
'set dollars = FormatCurrency(totalamt, 2, -1, 0, 1)
'Write the HTML to display the total pledge amount
Response.Write ("<br> $ ")
'Response.Write (dollars)
Response.Write (totalamt)
Response.Write ("<br>")
'Reset server objects
rsGiving.Close
Set rsGiving = Nothing
Set adoCon = Nothing
Set totalamt = Nothing
Set dollars = Nothing
%>
</body>
</html>
**********************************************
However, when I run this page, I get the following error:
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch
It occurs on the line with the calculations.
What do I need to change in order for this page to work correctly?
You should perform the calculation in the database. Save the following
query to your database.
[qryPledgeTotal]
SELECT
SUM(Gift_Amount * Gifts_Per_Year * (Matching_Gift_Ratio + 1)) AS
PledgeTotal
FROM
Giving
WHERE
Gift_Amount > 0 AND
Gifts_Per_Year > 0
Here's how you call it:
[PledgeTotal.asp]
<%
Dim sConn,cn,rs,PledgeTotal
sConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Careathon_SP2004.mdb")
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open sConn
cn.qryPledgeTotal rs
PledgeTotal = rs(0).Value
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
Response.Write PledgeTotal
%>
In answer to your original question, the error may be occurring because
you are referencing a field object and not it's value when performing
the calculation. Specifically the line in question should look like
this:
totalamt = totalamt + rsGiving("Gift_Amount").Value *
rsGiving("Gifts_Per_Year").Value *
(rsGiving("Matching_Gift_Ratio").Value + 1))
Note: Please consider using the native Jet OLE DB Provider. Here's a
related article: http://aspfaq.com/2126
HTH
-Chris Hohmann
I already had the query in the database. In the asp tutorials, I had gotten
the impression that I couldn't connect to Access queries, only tables. So,
I was trying to recreate the queries in asp, when all I had to do was
connect to to that query. Thanks for showing me that little bit of code
that makes it work.
"Chris Hohmann" <no****@thankyou.com> wrote in message
news:ey**************@TK2MSFTNGP12.phx.gbl... <tp******@kc.rr.com> wrote in message
news:Qx*******************@twister.rdc-kc.rr.com... I have an Access database used to track donor pledges. In it, there is
a table that contains three fields for each donor: Gift_Amount,
Gift_Per_Year, and Matching_Gift_Ratio.
The following formula would calculate the total pledge amount for each
donor:
(Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio + 1).
A total Pledge for all donors would just sum up the calculated values.
My goal is to create an ASP that would show this total amount pledged.
Here is the ASP that I have created so far:
***********************************************
<html>
<head>
<title>Pledge Totals</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsGiving 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database
Dim totalamt 'Holds the Total Pledge amount, a calculated field
Dim dollars 'Holds the formated currency amount
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less
connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Careathon_SP2004.mdb")
'Set an active connection to the Connection object using DSN
connection 'adoCon.Open "Careathon_SP2004"
'Create an ADO recordset object
Set rsGiving = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the
database strSQL = "SELECT Giving.Gift_Amount, Giving.Gifts_Per_Year,
Giving.Matching_Gift_Ratio FROM Giving WHERE Gift_Amount>0 AND
Gifts_Per_Year>0"
'Open the recordset with the SQL query
rsGiving.Open strSQL, adoCon
'Loop through the recordset
Do While not rsGiving.EOF
'Calculate total donor pledge and add to cumulative total - THIS IS
WHERE THE CODE STOPS AND THE ERROR OCCURS
totalamt = totalamt + ((rsGiving("Gift_Amount") *
rsGiving("Gifts_Per_Year")) * (rsGiving("Matching_Gift_Ratio") + 1))
'Move to the next record in the recordset
rsGiving.MoveNext
Loop
'Formats the total amount to currency
'set dollars = FormatCurrency(totalamt, 2, -1, 0, 1)
'Write the HTML to display the total pledge amount
Response.Write ("<br> $ ")
'Response.Write (dollars)
Response.Write (totalamt)
Response.Write ("<br>")
'Reset server objects
rsGiving.Close
Set rsGiving = Nothing
Set adoCon = Nothing
Set totalamt = Nothing
Set dollars = Nothing
%>
</body>
</html>
**********************************************
However, when I run this page, I get the following error:
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch
It occurs on the line with the calculations.
What do I need to change in order for this page to work correctly?
You should perform the calculation in the database. Save the following
query to your database.
[qryPledgeTotal]
SELECT
SUM(Gift_Amount * Gifts_Per_Year * (Matching_Gift_Ratio + 1)) AS
PledgeTotal
FROM
Giving
WHERE
Gift_Amount > 0 AND
Gifts_Per_Year > 0
Here's how you call it:
[PledgeTotal.asp]
<%
Dim sConn,cn,rs,PledgeTotal
sConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Careathon_SP2004.mdb")
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open sConn
cn.qryPledgeTotal rs
PledgeTotal = rs(0).Value
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
Response.Write PledgeTotal
%>
In answer to your original question, the error may be occurring because
you are referencing a field object and not it's value when performing
the calculation. Specifically the line in question should look like
this:
totalamt = totalamt + rsGiving("Gift_Amount").Value *
rsGiving("Gifts_Per_Year").Value *
(rsGiving("Matching_Gift_Ratio").Value + 1))
Note: Please consider using the native Jet OLE DB Provider. Here's a
related article:
http://aspfaq.com/2126
HTH
-Chris Hohmann
why are these answers in both groups ... dork
On Sat, 03 Jan 2004 22:40:48 GMT, <tp******@kc.rr.com> wrote: I have an Access database used to track donor pledges. In it, there is a
table that contains three fields for each donor: Gift_Amount, Gift_Per_Year,
and Matching_Gift_Ratio.
The following formula would calculate the total pledge amount for each
donor:
(Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio + 1).
A total Pledge for all donors would just sum up the calculated values.
My goal is to create an ASP that would show this total amount pledged. Here
is the ASP that I have created so far:
***********************************************
<html>
<head>
<title>Pledge Totals</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsGiving 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database
Dim totalamt 'Holds the Total Pledge amount, a calculated field
Dim dollars 'Holds the formated currency amount
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less
connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Careathon_SP2004.mdb")
'Set an active connection to the Connection object using DSN connection
'adoCon.Open "Careathon_SP2004"
'Create an ADO recordset object
Set rsGiving = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT Giving.Gift_Amount, Giving.Gifts_Per_Year,
Giving.Matching_Gift_Ratio FROM Giving WHERE Gift_Amount>0 AND
Gifts_Per_Year>0"
'Open the recordset with the SQL query
rsGiving.Open strSQL, adoCon
'Loop through the recordset
Do While not rsGiving.EOF
'Calculate total donor pledge and add to cumulative total - THIS IS WHERE
THE CODE STOPS AND THE ERROR OCCURS
totalamt = totalamt + ((rsGiving("Gift_Amount") *
rsGiving("Gifts_Per_Year")) * (rsGiving("Matching_Gift_Ratio") + 1))
'Move to the next record in the recordset
rsGiving.MoveNext
Loop
'Formats the total amount to currency
'set dollars = FormatCurrency(totalamt, 2, -1, 0, 1)
'Write the HTML to display the total pledge amount
Response.Write ("<br> $ ")
'Response.Write (dollars)
Response.Write (totalamt)
Response.Write ("<br>")
'Reset server objects
rsGiving.Close
Set rsGiving = Nothing
Set adoCon = Nothing
Set totalamt = Nothing
Set dollars = Nothing
%>
</body>
</html>
**********************************************
However, when I run this page, I get the following error:
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch
It occurs on the line with the calculations.
What do I need to change in order for this page to work correctly?
Hmm, somebody's compensating for an insufficiency
"Brynn" <z@z.com> wrote in message
news:3f**************@news.comcast.giganews.com... why are these answers in both groups ... dork
"Brynn" <z@z.com> wrote in message
news:3f**************@news.comcast.giganews.com... why are these answers in both groups ... dork
These answers are in both groups because the OP (original poster)
cross-posted to both the m.p.i.asp.db and m.p.i.asp.general groups. Had
the answer(s) not been posted to both groups, it's conceivable that
someone may have wasted their time by attempting to explore a solution
to a problem that had already been resolved.
Not quite bright enough to figure that one out, Brynn?
So, who's the loser - dork?
Bob Lehmann
"Brynn" <z@z.com> wrote in message
news:3f**************@news.comcast.giganews.com... why are these answers in both groups ... dork
On Sat, 03 Jan 2004 22:40:48 GMT, <tp******@kc.rr.com> wrote:
I have an Access database used to track donor pledges. In it, there is a
table that contains three fields for each donor: Gift_Amount,
Gift_Per_Year,and Matching_Gift_Ratio.
The following formula would calculate the total pledge amount for each
donor:
(Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio + 1).
A total Pledge for all donors would just sum up the calculated values.
My goal is to create an ASP that would show this total amount pledged.
Hereis the ASP that I have created so far:
***********************************************
<html>
<head>
<title>Pledge Totals</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsGiving 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database
Dim totalamt 'Holds the Total Pledge amount, a calculated field
Dim dollars 'Holds the formated currency amount
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less
connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Careathon_SP2004.mdb")
'Set an active connection to the Connection object using DSN connection
'adoCon.Open "Careathon_SP2004"
'Create an ADO recordset object
Set rsGiving = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the
databasestrSQL = "SELECT Giving.Gift_Amount, Giving.Gifts_Per_Year,
Giving.Matching_Gift_Ratio FROM Giving WHERE Gift_Amount>0 AND
Gifts_Per_Year>0"
'Open the recordset with the SQL query
rsGiving.Open strSQL, adoCon
'Loop through the recordset
Do While not rsGiving.EOF
'Calculate total donor pledge and add to cumulative total - THIS IS
WHERETHE CODE STOPS AND THE ERROR OCCURS
totalamt = totalamt + ((rsGiving("Gift_Amount") *
rsGiving("Gifts_Per_Year")) * (rsGiving("Matching_Gift_Ratio") + 1))
'Move to the next record in the recordset
rsGiving.MoveNext
Loop
'Formats the total amount to currency
'set dollars = FormatCurrency(totalamt, 2, -1, 0, 1)
'Write the HTML to display the total pledge amount
Response.Write ("<br> $ ")
'Response.Write (dollars)
Response.Write (totalamt)
Response.Write ("<br>")
'Reset server objects
rsGiving.Close
Set rsGiving = Nothing
Set adoCon = Nothing
Set totalamt = Nothing
Set dollars = Nothing
%>
</body>
</html>
**********************************************
However, when I run this page, I get the following error:
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch
It occurs on the line with the calculations.
What do I need to change in order for this page to work correctly?
I though tradition stated that you guys didn't answer cross-posting
spammer posts ... LOL
On Sat, 3 Jan 2004 22:23:26 -0700, "Bob Lehmann"
<no****@dontbotherme.zzz> wrote: Not quite bright enough to figure that one out, Brynn?
So, who's the loser - dork?
Bob Lehmann
"Brynn" <z@z.com> wrote in message
news:3f**************@news.comcast.giganews.com.. . why are these answers in both groups ... dork
On Sat, 03 Jan 2004 22:40:48 GMT, <tp******@kc.rr.com> wrote:
>I have an Access database used to track donor pledges. In it, there is a
>table that contains three fields for each donor: Gift_Amount,Gift_Per_Year, >and Matching_Gift_Ratio.
>
>The following formula would calculate the total pledge amount for each
>donor:
>(Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio + 1).
>
>A total Pledge for all donors would just sum up the calculated values.
>
>My goal is to create an ASP that would show this total amount pledged.Here >is the ASP that I have created so far:
>
>***********************************************
><html>
><head>
><title>Pledge Totals</title>
></head>
><body bgcolor="white" text="black">
><%
>'Dimension variables
>Dim adoCon 'Holds the Database Connection Object
>Dim rsGiving 'Holds the recordset for the records in the database
>Dim strSQL 'Holds the SQL query for the database
>Dim totalamt 'Holds the Total Pledge amount, a calculated field
>Dim dollars 'Holds the formated currency amount
>
>'Create an ADO connection odject
>Set adoCon = Server.CreateObject("ADODB.Connection")
>
>'Set an active connection to the Connection object using a DSN-less
>connection
>adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
>Server.MapPath("Careathon_SP2004.mdb")
>
>'Set an active connection to the Connection object using DSN connection
>'adoCon.Open "Careathon_SP2004"
>
>'Create an ADO recordset object
>Set rsGiving = Server.CreateObject("ADODB.Recordset")
>
>'Initialise the strSQL variable with an SQL statement to query thedatabase >strSQL = "SELECT Giving.Gift_Amount, Giving.Gifts_Per_Year,
>Giving.Matching_Gift_Ratio FROM Giving WHERE Gift_Amount>0 AND
>Gifts_Per_Year>0"
>
>'Open the recordset with the SQL query
>rsGiving.Open strSQL, adoCon
>
>'Loop through the recordset
>Do While not rsGiving.EOF
>
> 'Calculate total donor pledge and add to cumulative total - THIS ISWHERE >THE CODE STOPS AND THE ERROR OCCURS
> totalamt = totalamt + ((rsGiving("Gift_Amount") *
>rsGiving("Gifts_Per_Year")) * (rsGiving("Matching_Gift_Ratio") + 1))
>
> 'Move to the next record in the recordset
> rsGiving.MoveNext
>
>Loop
>
>'Formats the total amount to currency
>'set dollars = FormatCurrency(totalamt, 2, -1, 0, 1)
>
>'Write the HTML to display the total pledge amount
> Response.Write ("<br> $ ")
> 'Response.Write (dollars)
> Response.Write (totalamt)
> Response.Write ("<br>")
>
>
>'Reset server objects
>rsGiving.Close
>Set rsGiving = Nothing
>Set adoCon = Nothing
>Set totalamt = Nothing
>Set dollars = Nothing
>%>
></body>
></html>
>**********************************************
>
>However, when I run this page, I get the following error:
>
>Error Type:
>Microsoft VBScript runtime (0x800A000D)
>Type mismatch
>
>It occurs on the line with the calculations.
>
>What do I need to change in order for this page to work correctly?
>
>
>
I love you Bob ... could you go back to the thread about the trolls
and post some more ... that soap opera was too funny
On Sat, 3 Jan 2004 22:23:26 -0700, "Bob Lehmann"
<no****@dontbotherme.zzz> wrote: Not quite bright enough to figure that one out, Brynn?
So, who's the loser - dork?
Bob Lehmann
"Brynn" <z@z.com> wrote in message
news:3f**************@news.comcast.giganews.com.. . why are these answers in both groups ... dork
On Sat, 03 Jan 2004 22:40:48 GMT, <tp******@kc.rr.com> wrote:
>I have an Access database used to track donor pledges. In it, there is a
>table that contains three fields for each donor: Gift_Amount,Gift_Per_Year, >and Matching_Gift_Ratio.
>
>The following formula would calculate the total pledge amount for each
>donor:
>(Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio + 1).
>
>A total Pledge for all donors would just sum up the calculated values.
>
>My goal is to create an ASP that would show this total amount pledged.Here >is the ASP that I have created so far:
>
>***********************************************
><html>
><head>
><title>Pledge Totals</title>
></head>
><body bgcolor="white" text="black">
><%
>'Dimension variables
>Dim adoCon 'Holds the Database Connection Object
>Dim rsGiving 'Holds the recordset for the records in the database
>Dim strSQL 'Holds the SQL query for the database
>Dim totalamt 'Holds the Total Pledge amount, a calculated field
>Dim dollars 'Holds the formated currency amount
>
>'Create an ADO connection odject
>Set adoCon = Server.CreateObject("ADODB.Connection")
>
>'Set an active connection to the Connection object using a DSN-less
>connection
>adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
>Server.MapPath("Careathon_SP2004.mdb")
>
>'Set an active connection to the Connection object using DSN connection
>'adoCon.Open "Careathon_SP2004"
>
>'Create an ADO recordset object
>Set rsGiving = Server.CreateObject("ADODB.Recordset")
>
>'Initialise the strSQL variable with an SQL statement to query thedatabase >strSQL = "SELECT Giving.Gift_Amount, Giving.Gifts_Per_Year,
>Giving.Matching_Gift_Ratio FROM Giving WHERE Gift_Amount>0 AND
>Gifts_Per_Year>0"
>
>'Open the recordset with the SQL query
>rsGiving.Open strSQL, adoCon
>
>'Loop through the recordset
>Do While not rsGiving.EOF
>
> 'Calculate total donor pledge and add to cumulative total - THIS ISWHERE >THE CODE STOPS AND THE ERROR OCCURS
> totalamt = totalamt + ((rsGiving("Gift_Amount") *
>rsGiving("Gifts_Per_Year")) * (rsGiving("Matching_Gift_Ratio") + 1))
>
> 'Move to the next record in the recordset
> rsGiving.MoveNext
>
>Loop
>
>'Formats the total amount to currency
>'set dollars = FormatCurrency(totalamt, 2, -1, 0, 1)
>
>'Write the HTML to display the total pledge amount
> Response.Write ("<br> $ ")
> 'Response.Write (dollars)
> Response.Write (totalamt)
> Response.Write ("<br>")
>
>
>'Reset server objects
>rsGiving.Close
>Set rsGiving = Nothing
>Set adoCon = Nothing
>Set totalamt = Nothing
>Set dollars = Nothing
>%>
></body>
></html>
>**********************************************
>
>However, when I run this page, I get the following error:
>
>Error Type:
>Microsoft VBScript runtime (0x800A000D)
>Type mismatch
>
>It occurs on the line with the calculations.
>
>What do I need to change in order for this page to work correctly?
>
>
>
I have been on google doing a search for Bob Lehnmann ... from what I
can tell from all the posts ... it seems like it has just been the
past year where you turned into an asshole ... what has happened in
the last year to you Bob ... LOL
prick
On Sat, 3 Jan 2004 22:23:26 -0700, "Bob Lehmann"
<no****@dontbotherme.zzz> wrote: Not quite bright enough to figure that one out, Brynn?
So, who's the loser - dork?
Bob Lehmann
"Brynn" <z@z.com> wrote in message
news:3f**************@news.comcast.giganews.com.. . why are these answers in both groups ... dork
On Sat, 03 Jan 2004 22:40:48 GMT, <tp******@kc.rr.com> wrote:
>I have an Access database used to track donor pledges. In it, there is a
>table that contains three fields for each donor: Gift_Amount,Gift_Per_Year, >and Matching_Gift_Ratio.
>
>The following formula would calculate the total pledge amount for each
>donor:
>(Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio + 1).
>
>A total Pledge for all donors would just sum up the calculated values.
>
>My goal is to create an ASP that would show this total amount pledged.Here >is the ASP that I have created so far:
>
>***********************************************
><html>
><head>
><title>Pledge Totals</title>
></head>
><body bgcolor="white" text="black">
><%
>'Dimension variables
>Dim adoCon 'Holds the Database Connection Object
>Dim rsGiving 'Holds the recordset for the records in the database
>Dim strSQL 'Holds the SQL query for the database
>Dim totalamt 'Holds the Total Pledge amount, a calculated field
>Dim dollars 'Holds the formated currency amount
>
>'Create an ADO connection odject
>Set adoCon = Server.CreateObject("ADODB.Connection")
>
>'Set an active connection to the Connection object using a DSN-less
>connection
>adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
>Server.MapPath("Careathon_SP2004.mdb")
>
>'Set an active connection to the Connection object using DSN connection
>'adoCon.Open "Careathon_SP2004"
>
>'Create an ADO recordset object
>Set rsGiving = Server.CreateObject("ADODB.Recordset")
>
>'Initialise the strSQL variable with an SQL statement to query thedatabase >strSQL = "SELECT Giving.Gift_Amount, Giving.Gifts_Per_Year,
>Giving.Matching_Gift_Ratio FROM Giving WHERE Gift_Amount>0 AND
>Gifts_Per_Year>0"
>
>'Open the recordset with the SQL query
>rsGiving.Open strSQL, adoCon
>
>'Loop through the recordset
>Do While not rsGiving.EOF
>
> 'Calculate total donor pledge and add to cumulative total - THIS ISWHERE >THE CODE STOPS AND THE ERROR OCCURS
> totalamt = totalamt + ((rsGiving("Gift_Amount") *
>rsGiving("Gifts_Per_Year")) * (rsGiving("Matching_Gift_Ratio") + 1))
>
> 'Move to the next record in the recordset
> rsGiving.MoveNext
>
>Loop
>
>'Formats the total amount to currency
>'set dollars = FormatCurrency(totalamt, 2, -1, 0, 1)
>
>'Write the HTML to display the total pledge amount
> Response.Write ("<br> $ ")
> 'Response.Write (dollars)
> Response.Write (totalamt)
> Response.Write ("<br>")
>
>
>'Reset server objects
>rsGiving.Close
>Set rsGiving = Nothing
>Set adoCon = Nothing
>Set totalamt = Nothing
>Set dollars = Nothing
>%>
></body>
></html>
>**********************************************
>
>However, when I run this page, I get the following error:
>
>Error Type:
>Microsoft VBScript runtime (0x800A000D)
>Type mismatch
>
>It occurs on the line with the calculations.
>
>What do I need to change in order for this page to work correctly?
>
>
>
Brynn wrote: I though tradition stated that you guys didn't answer cross-posting
spammer posts ... LOL
No, it's multiposts that get our goats. I'll leave it to you to look up the
difference. ;-)
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Why don't you just do the calculation using the access?
for example :
"select ((giving.Gift_Amount * giving.Gift_Per_Year) *
(givingMatching_Gift_Ratio + 1)) as pledgeamount from giving"
it makes the work much easier and wastes less resources.
Roy.
"Chris Hohmann" <no****@thankyou.com> wrote in message
news:ey**************@TK2MSFTNGP12.phx.gbl... <tp******@kc.rr.com> wrote in message
news:Qx*******************@twister.rdc-kc.rr.com... I have an Access database used to track donor pledges. In it, there is
a table that contains three fields for each donor: Gift_Amount,
Gift_Per_Year, and Matching_Gift_Ratio.
The following formula would calculate the total pledge amount for each
donor:
(Gift_Amount * Gift_Per_Year) * (Matching_Gift_Ratio + 1).
A total Pledge for all donors would just sum up the calculated values.
My goal is to create an ASP that would show this total amount pledged.
Here is the ASP that I have created so far:
***********************************************
<html>
<head>
<title>Pledge Totals</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsGiving 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database
Dim totalamt 'Holds the Total Pledge amount, a calculated field
Dim dollars 'Holds the formated currency amount
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less
connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Careathon_SP2004.mdb")
'Set an active connection to the Connection object using DSN
connection 'adoCon.Open "Careathon_SP2004"
'Create an ADO recordset object
Set rsGiving = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the
database strSQL = "SELECT Giving.Gift_Amount, Giving.Gifts_Per_Year,
Giving.Matching_Gift_Ratio FROM Giving WHERE Gift_Amount>0 AND
Gifts_Per_Year>0"
'Open the recordset with the SQL query
rsGiving.Open strSQL, adoCon
'Loop through the recordset
Do While not rsGiving.EOF
'Calculate total donor pledge and add to cumulative total - THIS IS
WHERE THE CODE STOPS AND THE ERROR OCCURS
totalamt = totalamt + ((rsGiving("Gift_Amount") *
rsGiving("Gifts_Per_Year")) * (rsGiving("Matching_Gift_Ratio") + 1))
'Move to the next record in the recordset
rsGiving.MoveNext
Loop
'Formats the total amount to currency
'set dollars = FormatCurrency(totalamt, 2, -1, 0, 1)
'Write the HTML to display the total pledge amount
Response.Write ("<br> $ ")
'Response.Write (dollars)
Response.Write (totalamt)
Response.Write ("<br>")
'Reset server objects
rsGiving.Close
Set rsGiving = Nothing
Set adoCon = Nothing
Set totalamt = Nothing
Set dollars = Nothing
%>
</body>
</html>
**********************************************
However, when I run this page, I get the following error:
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch
It occurs on the line with the calculations.
What do I need to change in order for this page to work correctly?
You should perform the calculation in the database. Save the following
query to your database.
[qryPledgeTotal]
SELECT
SUM(Gift_Amount * Gifts_Per_Year * (Matching_Gift_Ratio + 1)) AS
PledgeTotal
FROM
Giving
WHERE
Gift_Amount > 0 AND
Gifts_Per_Year > 0
Here's how you call it:
[PledgeTotal.asp]
<%
Dim sConn,cn,rs,PledgeTotal
sConn = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Careathon_SP2004.mdb")
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open sConn
cn.qryPledgeTotal rs
PledgeTotal = rs(0).Value
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
Response.Write PledgeTotal
%>
In answer to your original question, the error may be occurring because
you are referencing a field object and not it's value when performing
the calculation. Specifically the line in question should look like
this:
totalamt = totalamt + rsGiving("Gift_Amount").Value *
rsGiving("Gifts_Per_Year").Value *
(rsGiving("Matching_Gift_Ratio").Value + 1))
Note: Please consider using the native Jet OLE DB Provider. Here's a
related article:
http://aspfaq.com/2126
HTH
-Chris Hohmann
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Kasp |
last post by:
Hi there,
I am trying to make an OLAP cube on a table having two columns (datetime,
Number_of_times_an_event_occured).
My dimension is time and I want to measure the Min and Max times an event...
|
by: Paul Mendez |
last post by:
Performance_Date SumOfBudget_NOI CurrYTD_BudgetNOI_Total
1/1/2004 $4,184,626.00 ?
2/1/2004 $4,484,710.00 ?
3/1/2004 $4,537,424.00 ?
4/1/2004 ...
|
by: Tony Williams |
last post by:
I have a table with two fields, txtvalue (a number field) and txtmonth ( a
date/time field). I want to create a report that shows the difference in
value between the value in txtvalue in one value...
|
by: kuhni |
last post by:
Hi everybody!
After searching newsgroups for a couple of hours, I now try asking
directly (running the risk of asking again the same question). My
problem is to predict when the size of the...
|
by: robin9876 |
last post by:
In an Access 2000 database on some forms 'Calculating...' is
continuously displayed in the status bar window and the text of the
control is automatically selected.
The only workaround is switching...
| | |
by: tkq |
last post by:
I have a normal table with customer information
I have a DoB field and I want to have an automatic calculation that will show
the age of the customer in another field next to it called Age
...
|
by: Nixeh |
last post by:
Hey guys,
New to the forums but ive hit a snag whilst updating a Access Database at work.
Currently trying to repair and maintain a database that has been built by 6 people all with even worse...
|
by: Lisa |
last post by:
In translating the formula for calculating lottery odds for various
conditions into a Visual Basic Program, I have apparently missed something
in that I get errors in the part of the calculation...
|
by: ye2127 |
last post by:
Hi,
I have two fields in my report. One of them is school name(the school name appears multiple times). The other field is class test score. How would I go about calculating the test average...
|
by: sumit kale |
last post by:
Hi,
Can somebody help me resolve my problem ?
I am getting error when calculating total using unbound textfiled in subform.
I have a main form called purchase_register_master and a subform...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |
