[AccessD] Function vs Sub

Susan,

Since you're doing essentially the same math to figure the percentage (group / whole) you could probably get by with one function passing in the SQL string to get the subset group.

<snip>
'Can be done via ADO as well
Function GetPercentage(sSQL As String) as Single
Dim db AS DAO.Database
Dim rs as Recordset
Dim lngWhole As Long
Dim lngGroup As Long
dim sngPercentage As Single
  Set db = CurrentDB
  'Get the count for the entire population
  set rs = db.OpenRecordset("TableOrQueryContainingWholeAmount")
  lngWhole = rs.RecordCount
  
  'get the count for the subset group
  set rs = db.OpenRecordset(sSQL)
  lngGroup = rs.RecordCount

  'do the math
  sngPercentage = (lngGroup / lngWhole) * 100

  GetPercentage = sngPercentage
End Function
</snip>

To use, put in a code module, declare a variable of type Single and call 

myvariable = GetPercentage("SELECT * FROM myTable WHERE Grade > = 'c'")

using whatever sql you come up with to get your subset.  myvariable will hold your percentage amount.  If you want the decimal percentage just remove the "* 100" from the function.

HTH,

Jim DeMarco
Director of Product Development
HealthSource/Hudson Health Plan


-----Original Message-----
From: Klos, Susan [mailto:Susan.Klos at fldoe.org]
Sent: Thursday, February 13, 2003 12:01 PM
To: 'accessd at databaseadvisors.com'
Subject: RE: [AccessD] Function vs Sub


I hope John doesn't mind but I have had a similar question and would like
some clarification.  From what folks have said, I would guess if I wanted to
return a percentage of a count of records, I would use a function.  Right?
Would I write a function for each percentage I wanted?  i.e. I want to get
the percentage of all elegible students who attained a grade of c or better
on the test.  Suppose I want to break this down further and get a percentage
of all elegible students who made a grade of c or better on the reading
test, the same for the math test, and the percentage of (students who
attained a score of >=3 plus students who attained a score of >=3.5) on the
writing test.  Would these be three different functions?  If so, and once I
have the functions created (and that is a big once as I am not sure how to
go about writing the functions in the first place) how or where do I call
these functions to get the results laid out in a grid so I can export them
to Excel.

-----Original Message-----
From: Susan Harkins [mailto:harkins at iglou.com]
Sent: Thursday, February 13, 2003 11:41 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Function vs Sub




> I recently smartened up and started using fuctions in my code, and I've
> got what may be a very dumb question...should I be using "functions" or
> should they be "Public Subs" or ?????? What is the difference. I
> apologize if I am lowering the intelligence quota today, but I just
> haven't come across this and I don't want to go down the wrong path.

==========Generally, a sub executes a task and a function returns a value --
theoretically. This is another one of those "you're wrong if you don't do it
like me" arguments. The truth is, Access doesn't really care.

I use subs only for event procedures and I use functions elsewhere. I manage
to deflect the arrows okay... ;)

Susan H.

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


***********************************************************************************
"This electronic message is intended to be for the use only of the named recipient, and may contain information from HealthSource/Hudson Health Plan (HS/HHP) that is confidential or privileged.  If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this message is strictly prohibited.  If you have received this message in error or are not the named recipient, please notify us immediately, either by contacting the sender at the electronic mail address noted above or calling HS/HHP at (914) 631-1611. If you are not the intended recipient, please do not forward this email to anyone, and delete and destroy all copies of this message.  Thank You".
***********************************************************************************