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_R atio.
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.CreateOb ject("ADODB.Con nection")
'Set an active connection to the Connection object using a DSN-less
connection
adoCon.Open "DRIVER={Micros oft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath( "Careathon_SP20 04.mdb")
'Set an active connection to the Connection object using DSN connection
'adoCon.Open "Careathon_SP20 04"
'Create an ADO recordset object
Set rsGiving = Server.CreateOb ject("ADODB.Rec ordset")
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT Giving.Gift_Amo unt, Giving.Gifts_Pe r_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("Gif t_Amount") *
rsGiving("Gifts _Per_Year")) * (rsGiving("Matc hing_Gift_Ratio ") + 1))
'Move to the next record in the recordset
rsGiving.MoveNe xt
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?
Jul 19 '05
13 2396
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****@dontbot herme.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.co mcast.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_Yea r, >and Matching_Gift_R atio. > >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>Pledg e 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.CreateOb ject("ADODB.Con nection") > >'Set an active connection to the Connection object using a DSN-less >connection >adoCon.Open "DRIVER={Micros oft Access Driver (*.mdb)}; DBQ=" & >Server.MapPath ("Careathon_SP2 004.mdb") > >'Set an active connection to the Connection object using DSN connection >'adoCon.Open "Careathon_SP20 04" > >'Create an ADO recordset object >Set rsGiving = Server.CreateOb ject("ADODB.Rec ordset") > >'Initialise the strSQL variable with an SQL statement to query thedatabase >strSQL = "SELECT Giving.Gift_Amo unt, Giving.Gifts_Pe r_Year, >Giving.Matchin g_Gift_Ratio FROM Giving WHERE Gift_Amount>0 AND >Gifts_Per_Year >0" > >'Open the recordset with the SQL query >rsGiving.Ope n 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("Gif t_Amount") * >rsGiving("Gift s_Per_Year")) * (rsGiving("Matc hing_Gift_Ratio ") + 1)) > > 'Move to the next record in the recordset > rsGiving.MoveNe xt > >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.Clo se >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****@dontbot herme.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.co mcast.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_Yea r, >and Matching_Gift_R atio. > >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>Pledg e 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.CreateOb ject("ADODB.Con nection") > >'Set an active connection to the Connection object using a DSN-less >connection >adoCon.Open "DRIVER={Micros oft Access Driver (*.mdb)}; DBQ=" & >Server.MapPath ("Careathon_SP2 004.mdb") > >'Set an active connection to the Connection object using DSN connection >'adoCon.Open "Careathon_SP20 04" > >'Create an ADO recordset object >Set rsGiving = Server.CreateOb ject("ADODB.Rec ordset") > >'Initialise the strSQL variable with an SQL statement to query thedatabase >strSQL = "SELECT Giving.Gift_Amo unt, Giving.Gifts_Pe r_Year, >Giving.Matchin g_Gift_Ratio FROM Giving WHERE Gift_Amount>0 AND >Gifts_Per_Year >0" > >'Open the recordset with the SQL query >rsGiving.Ope n 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("Gif t_Amount") * >rsGiving("Gift s_Per_Year")) * (rsGiving("Matc hing_Gift_Ratio ") + 1)) > > 'Move to the next record in the recordset > rsGiving.MoveNe xt > >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.Clo se >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_A mount * 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****@thankyo u.com> wrote in message
news:ey******** ******@TK2MSFTN GP12.phx.gbl... <tp******@kc.rr .com> wrote in message news:Qx******** ***********@twi ster.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_R atio.
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.CreateOb ject("ADODB.Con nection")
'Set an active connection to the Connection object using a DSN-less connection adoCon.Open "DRIVER={Micros oft Access Driver (*.mdb)}; DBQ=" & Server.MapPath( "Careathon_SP20 04.mdb")
'Set an active connection to the Connection object using DSN connection 'adoCon.Open "Careathon_SP20 04"
'Create an ADO recordset object Set rsGiving = Server.CreateOb ject("ADODB.Rec ordset")
'Initialise the strSQL variable with an SQL statement to query the database strSQL = "SELECT Giving.Gift_Amo unt, Giving.Gifts_Pe r_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("Gif t_Amount") * rsGiving("Gifts _Per_Year")) * (rsGiving("Matc hing_Gift_Ratio ") + 1))
'Move to the next record in the recordset rsGiving.MoveNe xt
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,Ple dgeTotal sConn = "DRIVER={Micros oft Access Driver (*.mdb)}; DBQ=" & Server.MapPath( "Careathon_SP20 04.mdb") Set cn = CreateObject("A DODB.Connection ") Set rs = CreateObject("A DODB.Recordset" ) cn.Open sConn cn.qryPledgeTot al 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").Val ue * (rsGiving("Matc hing_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
occured over time.
I have no problem in calculating Maximum occurance of events over time.
However, I have some NULL values in my Number_of_times_an_event_occured
column, due to which I don't see any result when I try to calculate
|
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 $4,826,850.00 ?
5/1/2004 $4,966,326.00 ?
Can someone help? What I am trying to do is create a query that will
end up looking like the bottom example. The above query is a
calculated totals query with an...
|
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 of txtmonth and the value
of txtvalue in another value of txtmonth and the percentage increase . For
example if I have the value 1000 in 30/03/03 and the value 1100 in 30/03/04
How do I calculate the difference as 100 and the increase as 10%. I...
|
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 database (1GB I expect) is
over 1GB by calculating the maximal number of data sets (tuple) added:
1. Is the following calculation for estimating the mdb file size in
Access 97 correct:
1 integer field = 4 Byte
|
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 to another application and back
between each field that data is entered.
I have tried this database on pc's that have office 2000 SP1a and
SP3.
How do investigate what is causing this calculating issue and how do I
resolve it?
| |
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
Could you please help me with this
Thanx all
|
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 knowledge of access then me and its becoming a pain at least.
My boss has just said that she wants a new field that displays the date of the patients 55th Birthday.
Problem is there are 1500+ records on this database and ive had a play around...
|
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 where the number of ways
of failure (pFal) is calculated
Both errors happen in the code section
x1 = Draw - MatchesReq
x2 = Field - Selections
For Counter = 1 To (Draw - MatchesReq - 1)
x1 = x1 * (Draw - MatchesReq - Counter)
|
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 for each school?
I can calculate the average of all the schools but am having difficulty coding some kind of sort procedure for an average test score of all the classes in each school.
Thanks...
|
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 called purchase_register_details, In sub form i have a unbound textfield called txt_Total_Amount where in i am calculating total amount for different items purchased by giving following code in control source.
ContolSource: =Sum()
In my main form i...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
| |
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 then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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 last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |