Count Function

1. Senior Member
Join Date
Dec 1969
Posts
2,880

## Count Function

Hi,<BR><BR>I&#039;m trying to get the average number of contacts which is to be divided in the following manner. <BR><BR>Sum Total Dollar Value of Contacts/ Sum of Contacts<BR><BR>I&#039;m encapsulating the code using VB and provided here is my code. Could someone tell me what I&#039;m doing wrong since it doesn&#039;t seem to work. How do I COUNT the SUM of Contacts that equal current? Do I still have to peform a RSFIND.EOF and loop it? <BR><BR>Public Function AverageContacts(MemberID, StartDate, EndDate)<BR> Dim Conn As New ADODB.Connection<BR> Dim RSFind As ADODB.Recordset<BR> Dim Result As String<BR> Dim CurrentTotalAmount As Single<BR> Dim GetTotalAmount As Single<BR> Conn.Open LocalDSN, LocalUserName, LocalPassword<BR> Set RSFind = Conn.Execute("Select * " _<BR> & "From Contacts where " _<BR> & "Status = ""Current"" AND " _<BR> & "OrderNo BETWEEN &#039;" & StartDate & "&#039;" _<BR> & "AND &#039;" & EndDate & "&#039; And " _<BR> & "MemberID = " & MemberID)<BR> <BR> CurrentTotalAmount = CurrentTotalAmount + (RSFind("CurrentAmount") And RSFind("CurrentAmount"))<BR> GetTotalAmount = CurrentTotalAmount<BR> <BR> Result = Result & "&#060;div align=""center""&#062;&#060;font size=""4""&#062;Average Amount:" _<BR> & "& FormatCurrency(GetTotalOwing) & " _<BR> & "&#060;/font&#062;&#060;/div&#062;"<BR> <BR> AverageContacts = Result<BR>End Function

2. Senior Member
Join Date
Dec 1969
Posts
623

## RE: Count Function

&#062;CurrentTotalAmount = CurrentTotalAmount + (RSFind("CurrentAmount") And RSFind("CurrentAmount")) <BR><BR>yes this has to be in the loop<BR><BR>did you know that there is a SQL function called AVG ?<BR><BR>SELECT AVG ( currentAmount ) ...<BR><BR><BR>&#062;Result = Result & "&#060;div align=""center""&#062;&#060;font <BR>&#062;size=""4""&#062;Average Amount:" _ <BR>&#062; & "& FormatCurrency(GetTotalOwing) & " _ <BR>&#062; & "&#060;/font&#062;&#060;/div&#062;" <BR><BR>what is getTotalOwing? is that a typo<BR><BR>&#062;& "Status = ""Current"" AND " _<BR><BR>that puts a double quote as the string delimiter, sql server expects a single quote<BR><BR><BR>do you get an error message?

3. Senior Member
Join Date
Dec 1969
Posts
2,880

## Error Message

Hi CoolDude,<BR><BR>Thanks for your input. I did know there was AVG function in SQL however upon the implementation of eithre AVG and COUNT I seem to get the following message:<BR><BR>ADODB.Fields error &#039;800a0cc1&#039; <BR><BR>Item cannot be found in the collection corresponding to the requested name or ordinal. <BR><BR>The row does exist. I&#039;m not sure why I am experiencing the following error.<BR><BR>The GetTotalOwing was a typographical error. Sorry. It was supposed to be GetTotalAmount <BR><BR>I have placed double quotes since the call represents checking for all values that equal to Current in the database.

4. Senior Member
Join Date
Dec 1969
Posts
2,437

## RE: Error Message

When using aggregate functions (AVG,SUM etc) the result will not have a name<BR><BR>i.e. if you have<BR><BR>set rs = connect.execute("select avg(c1) from t")<BR>a = rs("c1")<BR><BR>You will get the error you specified. You can either do<BR><BR>set rs = connect.execute("select avg(c1) from t")<BR>a = rs(0)<BR><BR>or<BR><BR>set rs = connect.execute("select avg(c1) as average from t")<BR>a = rs("average")<BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•