Tom Adams
tomadatn at bellsouth.net
Thu Feb 13 12:03:42 CST 2003
Actually Subs can return a value - by leaving the incoming
parameters ByRef and changing their value in the sub.
I've stayed away from this for a long time then had several instances
where I badly wanted to do it - so I did. I didn't lose any hair, haven't
grown a 3rd arm and smell no worse than before.
Tom (I always use functions anyway!) Adams
----- Original Message -----
From: "Jim DeMarco" <Jdemarco at hshhp.org>
To: <accessd at databaseadvisors.com>
Sent: Thursday, February 13, 2003 11:43 AM
Subject: RE: [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".
> ***********************************************************************************
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com