Calculating field in ASP

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 (" $ ")
'Response.Write (dollars)
Response.Write (totalamt)
Response.Write (" ")

'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?

Cowboy \(Gregory A. Beamer\) · Cowboy \(Gregory A. Beamer\)

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!
************************************************** ********************
<tpscharf@kc.rr.com> wrote in message
news:QxHJb.38591$fq1.29984@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 (" $ ")
> 'Response.Write (dollars)
> Response.Write (totalamt)
> Response.Write (" ")
>
>
> '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?
>
>
>

tpscharf@kc.rr.com · tpscharf@kc.rr.com

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)" <NoSpamMgbworld@comcast.netNoSpamM> wrote in
message news:eV74UEl0DHA.3140@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!
> ************************************************** ********************

Chris Hohmann · Chris Hohmann

<tpscharf@kc.rr.com> wrote in message
news:QxHJb.38591$fq1.29984@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 (" $ ")
> 'Response.Write (dollars)
> Response.Write (totalamt)
> Response.Write (" ")
>
>
> '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:
[url]http://aspfaq.com/2126[/url]

HTH
-Chris Hohmann

tpscharf@kc.rr.com · tpscharf@kc.rr.com

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" <nospam@thankyou.com> wrote in message
news:eyOkYYl0DHA.2156@TK2MSFTNGP12.phx.gbl...

> <tpscharf@kc.rr.com> wrote in message
> news:QxHJb.38591$fq1.29984@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 (" $ ")
> > 'Response.Write (dollars)
> > Response.Write (totalamt)
> > Response.Write (" ")
> >
> >
> > '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:
> [url]http://aspfaq.com/2126[/url]
>
> HTH
> -Chris Hohmann
>
>
>
>
>
>
>
>
>

Brynn · Brynn

why are these answers in both groups ... dork

On Sat, 03 Jan 2004 22:40:48 GMT, <tpscharf@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 (" $ ")
> 'Response.Write (dollars)
> Response.Write (totalamt)
> Response.Write (" ")
>
>
>'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?
>
>
>

Postmaster · Postmaster

Hmm, somebody's compensating for an insufficiency

"Brynn" <z@z.com> wrote in message
news:3ff77ba0.2043929@news.comcast.giganews.com...

> why are these answers in both groups ... dork
>

Chris Hohmann · Chris Hohmann

"Brynn" <z@z.com> wrote in message
news:3ff77ba0.2043929@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.

Bob Lehmann · Bob Lehmann

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:3ff77ba0.2043929@news.comcast.giganews.com...

> why are these answers in both groups ... dork
>
>
> On Sat, 03 Jan 2004 22:40:48 GMT, <tpscharf@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 (" $ ")
> > 'Response.Write (dollars)
> > Response.Write (totalamt)
> > Response.Write (" ")
> >
> >
> >'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 · Brynn

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"
<nospam@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:3ff77ba0.2043929@news.comcast.giganews.com.. .

>> why are these answers in both groups ... dork
>>
>>
>> On Sat, 03 Jan 2004 22:40:48 GMT, <tpscharf@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 (" $ ")
>> > 'Response.Write (dollars)
>> > Response.Write (totalamt)
>> > Response.Write (" ")
>> >
>> >
>> >'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 · Brynn

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"
<nospam@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:3ff77ba0.2043929@news.comcast.giganews.com.. .

>> why are these answers in both groups ... dork
>>
>>
>> On Sat, 03 Jan 2004 22:40:48 GMT, <tpscharf@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 (" $ ")
>> > 'Response.Write (dollars)
>> > Response.Write (totalamt)
>> > Response.Write (" ")
>> >
>> >
>> >'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 · Brynn

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"
<nospam@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:3ff77ba0.2043929@news.comcast.giganews.com.. .

>> why are these answers in both groups ... dork
>>
>>
>> On Sat, 03 Jan 2004 22:40:48 GMT, <tpscharf@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 (" $ ")
>> > 'Response.Write (dollars)
>> > Response.Write (totalamt)
>> > Response.Write (" ")
>> >
>> >
>> >'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?
>> >
>> >
>> >

>>

>
>

Bob Barrows · Bob Barrows

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"

Roy Danon · Roy Danon

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" <nospam@thankyou.com> wrote in message
news:eyOkYYl0DHA.2156@TK2MSFTNGP12.phx.gbl...

> <tpscharf@kc.rr.com> wrote in message
> news:QxHJb.38591$fq1.29984@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 (" $ ")
> > 'Response.Write (dollars)
> > Response.Write (totalamt)
> > Response.Write (" ")
> >
> >
> > '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:
> [url]http://aspfaq.com/2126[/url]
>
> HTH
> -Chris Hohmann
>
>
>
>
>
>
>
>
>

Calculating field in ASP

Thread Tools

Display

Calculating field in ASP

Similar Questions and Discussions

Calculating field - minus??

Calculating age

Acrobat 5.0 Calculating form field data

Calculating

Calculating a field

Re: Calculating field in ASP

Re: Calculating field in ASP

Re: Calculating field in ASP

Re: Calculating field in ASP

Re: Calculating field in ASP

Re: Calculating field in ASP

Re: Calculating field in ASP

Re: Calculating field in ASP

Re: Calculating field in ASP

Re: Calculating field in ASP

Re: Calculating field in ASP

Re: Calculating field in ASP

Re: Calculating field in ASP

Posting Permissions