Quartile function

  • Thread starter Thread starter Meg
  • Start date Start date
M

Meg

I am trying to use the quartile function in a report
footer to calculate Q1, mediam and Q3 {=quartile
([field],1)}
When the report is opened, access prompts me for the
parameter value 'quartile'. The median function has the
same result, and the report will not preview the last page
of my report without freezing.

Access help states that 'you may need to install
msowcf.dll' but this is already in place.

Any other suggestions?

Thanks
Meg
 
Meg,

QUARTILE and MEDIAN are Excel "worksheet functions," not VBA or Access
functions. Although there are a couple of exceptions, when you see a
function name that is all uppercase in help it is usually a worksheet
function. VBA and Access functions are typically mixed case; i.e. DateAdd,
DatePart, and MonthName. There are a few functions that have both worksheet
and VBA versions. An example would be the "principal payment" function. The
spreadsheet version of is "PPMT" and the VBA version is "PPmt."

The problem is that spreadsheet functions can not be used directly in Access
and VBA.

There is good news and bad news and bad news.

The good news is that it's fairly easy to call Excel worksheet functions
from Access through VBA. See the article at
http://support.microsoft.com/default.aspx?scid=kb;en-us;198571.

Bad news #1: Although calling them from VBA is fairly easy, you can't use
them like "aggregate" functions in queries and on reports.

Bad news #2: A lot of worksheet functions need several values in a single
argument which are passed as ranges of cells in Excel. Access is a database,
not a spreadsheet, so there is no concept of ranges per se.

Bad news #2 can be overcome, however, it does take a bit of VBA code. You
need to write some code that loops through the records that contain your
values and load them into an array variable which can then be used in place
of a range object.

The other option is to write your own replacement function. Although I've
never seen one for the Quartile function, there is one for the Median
function described in the article at
http://support.microsoft.com/default.aspx?scid=kb;en-us;210581. The Excel
algorithm for Quartile is given in the article at
http://support.microsoft.com/default.aspx?scid=kb;en-us;214072, so writing a
replacement function like the one for Median shouldn't be that tough.

Hope this gives some insight as to why you are getting your error and the
possible solutions.

Good luck.

Sco
 
This, or a related question, comes up with monotonous regularity,
since (as noted in another reply) Access does not have native
functions for this class of statistics. Some time ago, I created a
Class to provide the missing functionality. Rather than upsetting
people by posting what would be an even longer message and occupying
bandwidth unnecessarily, I have uploaded a zip file to my business
website. The file contains the Class as a .cls file and a small
database that demonstrates its use in a report.

The link is http://www.parish-data.com/data/cumstats.zip

The externally visible Methods of the Class are as follows:
AddItem(VarValue as Variant) - Adds an value to the internal list of
values of which the Median, first Decile, or whatever boundary value
you want will be returned. If you pass AddItem a Null it will simply
be ignored. The data type (Date, Integer, String, etc.) of the first
value added to the list is recorded. Subsequent values added must be
of the same type, or you will get an error box and the new item will
not be added.
AddItems(VarArray as Variant | VarValue1, VarValue2,....,VarValueN as
Variants) - Adds multiple values to the list. Accepts either an Array
containing multiple values or a list of values. This calls AddItem
internally, so it follows the same rules regarding data types.
Median - Returns a Variant containing the median value from the list.
This is written as a Method but can be regarded as a readonly
Property. If there are less than 2 * BucketSize (see below) total
values in the list, Median returns Null.
Slice(NSlices as Integer, SliceNumber as Integer) - Returns a Variant
containing the specified boundary value. This does quartiles, deciles,
or whatever you want - for the first quartile boundary value you would
invoke it as Slice(4,1). Median converts internally into Slice(2,1).
If the number of values in the list divided by NSlices is less than
than BucketSize (see below), Slice returns Null, as it does if
SliceNumber>= NSlices, or either argument is <1.

The externally visible Properties of the Class are:
Count - Returns a Long containing the number of values in the list.
BucketSize - Gets or Sets the integer number of values in a slice
below which Slice (or Median) will not return a value. This is
read/write and the default value is 3. The object of this is to avoid
reporting stupid statistics (like the 4th decile boundary of a list of
8 values!).

Let me know if you have any problems with it.


I am trying to use the quartile function in a report
footer to calculate Q1, mediam and Q3 {=quartile
([field],1)}
When the report is opened, access prompts me for the
parameter value 'quartile'. The median function has the
same result, and the report will not preview the last page
of my report without freezing.

Access help states that 'you may need to install
msowcf.dll' but this is already in place.

Any other suggestions?

Thanks
Meg
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
This, or a related question, comes up with monotonous regularity,
since (as noted in another reply) Access does not have native
functions for this class of statistics. Some time ago, I created a
Class to provide the missing functionality. Rather than upsetting
people by posting what would be an even longer message and occupying
bandwidth unnecessarily, I have uploaded a zip file to my business
website. The file contains the Class as a .cls file and a small
database that demonstrates its use in a report.

The link is http://www.parish-data.com/data/cumstats.zip

The externally visible Methods of the Class are as follows:
AddItem(VarValue as Variant) - Adds an value to the internal list of
values of which the Median, first Decile, or whatever boundary value
you want will be returned. If you pass AddItem a Null it will simply
be ignored. The data type (Date, Integer, String, etc.) of the first
value added to the list is recorded. Subsequent values added must be
of the same type, or you will get an error box and the new item will
not be added.
AddItems(VarArray as Variant | VarValue1, VarValue2,....,VarValueN as
Variants) - Adds multiple values to the list. Accepts either an Array
containing multiple values or a list of values. This calls AddItem
internally, so it follows the same rules regarding data types.
Median - Returns a Variant containing the median value from the list.
This is written as a Method but can be regarded as a readonly
Property. If there are less than 2 * BucketSize (see below) total
values in the list, Median returns Null.
Slice(NSlices as Integer, SliceNumber as Integer) - Returns a Variant
containing the specified boundary value. This does quartiles, deciles,
or whatever you want - for the first quartile boundary value you would
invoke it as Slice(4,1). Median converts internally into Slice(2,1).
If the number of values in the list divided by NSlices is less than
than BucketSize (see below), Slice returns Null, as it does if
SliceNumber>= NSlices, or either argument is <1.

The externally visible Properties of the Class are:
Count - Returns a Long containing the number of values in the list.
BucketSize - Gets or Sets the integer number of values in a slice
below which Slice (or Median) will not return a value. This is
read/write and the default value is 3. The object of this is to avoid
reporting stupid statistics (like the 4th decile boundary of a list of
8 values!).

Let me know if you have any problems with it.


I am trying to use the quartile function in a report
footer to calculate Q1, mediam and Q3 {=quartile
([field],1)}
When the report is opened, access prompts me for the
parameter value 'quartile'. The median function has the
same result, and the report will not preview the last page
of my report without freezing.

Access help states that 'you may need to install
msowcf.dll' but this is already in place.

Any other suggestions?

Thanks
Meg
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Gee...I dont feel very silly at all! Trying to use excel
functions in access without even realising! Weird that
its included in access help without mentioning that it
doesnt actually work in access.

Thanks very much for your help Peter and Sco.

Peter, I have downloaded your sample database, and have
used your code to display median, Q1 and Q3 in most of my
existing reports.
The only trouble I have is when I have a report that does
not include any grouping headers. When this is the case I
get error 91. Is there a requirement to have the data
grouped? If so I may need to force some artificail or
meaningless groupings.

Thanks again,
cheers,
Meg

-----Original Message-----
This, or a related question, comes up with monotonous regularity,
since (as noted in another reply) Access does not have native
functions for this class of statistics. Some time ago, I created a
Class to provide the missing functionality. Rather than upsetting
people by posting what would be an even longer message and occupying
bandwidth unnecessarily, I have uploaded a zip file to my business
website. The file contains the Class as a .cls file and a small
database that demonstrates its use in a report.

The link is http://www.parish-data.com/data/cumstats.zip

The externally visible Methods of the Class are as follows:
AddItem(VarValue as Variant) - Adds an value to the internal list of
values of which the Median, first Decile, or whatever boundary value
you want will be returned. If you pass AddItem a Null it will simply
be ignored. The data type (Date, Integer, String, etc.) of the first
value added to the list is recorded. Subsequent values added must be
of the same type, or you will get an error box and the new item will
not be added.
AddItems(VarArray as Variant | VarValue1, VarValue2,....,VarValueN as
Variants) - Adds multiple values to the list. Accepts either an Array
containing multiple values or a list of values. This calls AddItem
internally, so it follows the same rules regarding data types.
Median - Returns a Variant containing the median value from the list.
This is written as a Method but can be regarded as a readonly
Property. If there are less than 2 * BucketSize (see below) total
values in the list, Median returns Null.
Slice(NSlices as Integer, SliceNumber as Integer) - Returns a Variant
containing the specified boundary value. This does quartiles, deciles,
or whatever you want - for the first quartile boundary value you would
invoke it as Slice(4,1). Median converts internally into Slice(2,1).
If the number of values in the list divided by NSlices is less than
than BucketSize (see below), Slice returns Null, as it does if
SliceNumber>= NSlices, or either argument is <1.

The externally visible Properties of the Class are:
Count - Returns a Long containing the number of values in the list.
BucketSize - Gets or Sets the integer number of values in a slice
below which Slice (or Median) will not return a value. This is
read/write and the default value is 3. The object of this is to avoid
reporting stupid statistics (like the 4th decile boundary of a list of
8 values!).

Let me know if you have any problems with it.


I am trying to use the quartile function in a report
footer to calculate Q1, mediam and Q3 {=quartile
([field],1)}
When the report is opened, access prompts me for the
parameter value 'quartile'. The median function has the
same result, and the report will not preview the last page
of my report without freezing.

Access help states that 'you may need to install
msowcf.dll' but this is already in place.

Any other suggestions?

Thanks
Meg
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored- Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---
.
Click to expand...
 
There should be no requirement to have the data grouped. Indeed, I
also use the Class in VBA contexts, outside Reports. In my example
database, if you are only interested in summary statistics over the
entire data set, you only need the code and controls that reference
the Class instances with names ending in Gbl. I wonder if you have a
variable naming problems - perhaps you are (e.g.) initialising one
Class instance and then trying to use another. Is there a description
for "Error 91"?

Gee...I dont feel very silly at all! Trying to use excel
functions in access without even realising! Weird that
its included in access help without mentioning that it
doesnt actually work in access.

Thanks very much for your help Peter and Sco.

Peter, I have downloaded your sample database, and have
used your code to display median, Q1 and Q3 in most of my
existing reports.
The only trouble I have is when I have a report that does
not include any grouping headers. When this is the case I
get error 91. Is there a requirement to have the data
grouped? If so I may need to force some artificail or
meaningless groupings.

Thanks again,
cheers,
Meg

-----Original Message-----
This, or a related question, comes up with monotonous regularity,
since (as noted in another reply) Access does not have native
functions for this class of statistics. Some time ago, I created a
Class to provide the missing functionality. Rather than upsetting
people by posting what would be an even longer message and occupying
bandwidth unnecessarily, I have uploaded a zip file to my business
website. The file contains the Class as a .cls file and a small
database that demonstrates its use in a report.

The link is http://www.parish-data.com/data/cumstats.zip

The externally visible Methods of the Class are as follows:
AddItem(VarValue as Variant) - Adds an value to the internal list of
values of which the Median, first Decile, or whatever boundary value
you want will be returned. If you pass AddItem a Null it will simply
be ignored. The data type (Date, Integer, String, etc.) of the first
value added to the list is recorded. Subsequent values added must be
of the same type, or you will get an error box and the new item will
not be added.
AddItems(VarArray as Variant | VarValue1, VarValue2,....,VarValueN as
Variants) - Adds multiple values to the list. Accepts either an Array
containing multiple values or a list of values. This calls AddItem
internally, so it follows the same rules regarding data types.
Median - Returns a Variant containing the median value from the list.
This is written as a Method but can be regarded as a readonly
Property. If there are less than 2 * BucketSize (see below) total
values in the list, Median returns Null.
Slice(NSlices as Integer, SliceNumber as Integer) - Returns a Variant
containing the specified boundary value. This does quartiles, deciles,
or whatever you want - for the first quartile boundary value you would
invoke it as Slice(4,1). Median converts internally into Slice(2,1).
If the number of values in the list divided by NSlices is less than
than BucketSize (see below), Slice returns Null, as it does if
SliceNumber>= NSlices, or either argument is <1.

The externally visible Properties of the Class are:
Count - Returns a Long containing the number of values in the list.
BucketSize - Gets or Sets the integer number of values in a slice
below which Slice (or Median) will not return a value. This is
read/write and the default value is 3. The object of this is to avoid
reporting stupid statistics (like the 4th decile boundary of a list of
8 values!).

Let me know if you have any problems with it.


I am trying to use the quartile function in a report
footer to calculate Q1, mediam and Q3 {=quartile
([field],1)}
When the report is opened, access prompts me for the
parameter value 'quartile'. The median function has the
same result, and the report will not preview the last page
of my report without freezing.

Access help states that 'you may need to install
msowcf.dll' but this is already in place.

Any other suggestions?

Thanks
Meg
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored- Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---
.
Click to expand...
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Error 91 is "object variable or with block variable not
set". I have had this before for a variety of reasons.
I seem to have got around that one now (dont know how!),
but am now experiencing a different issue.

When the report previews it gives me an incorrect median,
Q1 and Q3, but when it prints I get the correct values on
the printout!

I have moved the Detail_Print code to the OnFormat event
instead, and this seems to work....but would doing this
affect anything else?

Thanks!

-----Original Message-----
There should be no requirement to have the data grouped. Indeed, I
also use the Class in VBA contexts, outside Reports. In my example
database, if you are only interested in summary statistics over the
entire data set, you only need the code and controls that reference
the Class instances with names ending in Gbl. I wonder if you have a
variable naming problems - perhaps you are (e.g.) initialising one
Class instance and then trying to use another. Is there a description
for "Error 91"?

Gee...I dont feel very silly at all! Trying to use excel
functions in access without even realising! Weird that
its included in access help without mentioning that it
doesnt actually work in access.

Thanks very much for your help Peter and Sco.

Peter, I have downloaded your sample database, and have
used your code to display median, Q1 and Q3 in most of my
existing reports.
The only trouble I have is when I have a report that does
not include any grouping headers. When this is the case I
get error 91. Is there a requirement to have the data
grouped? If so I may need to force some artificail or
meaningless groupings.

Thanks again,
cheers,
Meg

-----Original Message-----
This, or a related question, comes up with monotonous regularity,
since (as noted in another reply) Access does not have native
functions for this class of statistics. Some time ago,
Click to expand...
I
created a
Class to provide the missing functionality. Rather than upsetting
people by posting what would be an even longer message and occupying
bandwidth unnecessarily, I have uploaded a zip file to
Click to expand...
my
business
website. The file contains the Class as a .cls file and
Click to expand...
a
small
database that demonstrates its use in a report.

The link is http://www.parish-data.com/data/cumstats.zip

The externally visible Methods of the Class are as follows:
AddItem(VarValue as Variant) - Adds an value to the internal list of
values of which the Median, first Decile, or whatever boundary value
you want will be returned. If you pass AddItem a Null
Click to expand...
it
will simply
be ignored. The data type (Date, Integer, String, etc.) of the first
value added to the list is recorded. Subsequent values added must be
of the same type, or you will get an error box and the new item will
not be added.
AddItems(VarArray as Variant | VarValue1, VarValue2,....,VarValueN as
Variants) - Adds multiple values to the list. Accepts either an Array
containing multiple values or a list of values. This calls AddItem
internally, so it follows the same rules regarding data types.
Median - Returns a Variant containing the median value from the list.
This is written as a Method but can be regarded as a readonly
Property. If there are less than 2 * BucketSize (see below) total
values in the list, Median returns Null.
Slice(NSlices as Integer, SliceNumber as Integer) - Returns a Variant
containing the specified boundary value. This does quartiles, deciles,
or whatever you want - for the first quartile boundary value you would
invoke it as Slice(4,1). Median converts internally
Click to expand...
into
Slice(2,1).
If the number of values in the list divided by NSlices
Click to expand...
is
less than
than BucketSize (see below), Slice returns Null, as it does if
SliceNumber>= NSlices, or either argument is <1.

The externally visible Properties of the Class are:
Count - Returns a Long containing the number of values
Click to expand...
in
the list.
BucketSize - Gets or Sets the integer number of values
Click to expand...
in
a slice
below which Slice (or Median) will not return a value. This is
read/write and the default value is 3. The object of
Click to expand...
this
is to avoid
reporting stupid statistics (like the 4th decile
Click to expand...
boundary
of a list of
8 values!).

Let me know if you have any problems with it.


On Wed, 30 Jun 2004 17:39:35 -0700, "Meg"


I am trying to use the quartile function in a report
footer to calculate Q1, mediam and Q3 {=quartile
([field],1)}
When the report is opened, access prompts me for the
parameter value 'quartile'. The median function has the
same result, and the report will not preview the last page
of my report without freezing.

Access help states that 'you may need to install
msowcf.dll' but this is already in place.

Any other suggestions?

Thanks
Meg


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored- Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---
.
Click to expand...
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored- Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---
.
Click to expand...
 
I also notice that I get different results depending on
whether I display the median etc in group footers, or if I
only display an overall median in the report footer.
With the group footers, it is sometimes giving me a group
median that is outside of the range of values. (ie data
values ranging between 6.55 and 7.87, and median is
displayed as 8.17)

....think I may have done something wrong.


-----Original Message-----
There should be no requirement to have the data grouped. Indeed, I
also use the Class in VBA contexts, outside Reports. In my example
database, if you are only interested in summary statistics over the
entire data set, you only need the code and controls that reference
the Class instances with names ending in Gbl. I wonder if you have a
variable naming problems - perhaps you are (e.g.) initialising one
Class instance and then trying to use another. Is there a description
for "Error 91"?

Gee...I dont feel very silly at all! Trying to use excel
functions in access without even realising! Weird that
its included in access help without mentioning that it
doesnt actually work in access.

Thanks very much for your help Peter and Sco.

Peter, I have downloaded your sample database, and have
used your code to display median, Q1 and Q3 in most of my
existing reports.
The only trouble I have is when I have a report that does
not include any grouping headers. When this is the case I
get error 91. Is there a requirement to have the data
grouped? If so I may need to force some artificail or
meaningless groupings.

Thanks again,
cheers,
Meg

-----Original Message-----
This, or a related question, comes up with monotonous regularity,
since (as noted in another reply) Access does not have native
functions for this class of statistics. Some time ago,
Click to expand...
I
created a
Class to provide the missing functionality. Rather than upsetting
people by posting what would be an even longer message and occupying
bandwidth unnecessarily, I have uploaded a zip file to
Click to expand...
my
business
website. The file contains the Class as a .cls file and
Click to expand...
a
small
database that demonstrates its use in a report.

The link is http://www.parish-data.com/data/cumstats.zip

The externally visible Methods of the Class are as follows:
AddItem(VarValue as Variant) - Adds an value to the internal list of
values of which the Median, first Decile, or whatever boundary value
you want will be returned. If you pass AddItem a Null
Click to expand...
it
will simply
be ignored. The data type (Date, Integer, String, etc.) of the first
value added to the list is recorded. Subsequent values added must be
of the same type, or you will get an error box and the new item will
not be added.
AddItems(VarArray as Variant | VarValue1, VarValue2,....,VarValueN as
Variants) - Adds multiple values to the list. Accepts either an Array
containing multiple values or a list of values. This calls AddItem
internally, so it follows the same rules regarding data types.
Median - Returns a Variant containing the median value from the list.
This is written as a Method but can be regarded as a readonly
Property. If there are less than 2 * BucketSize (see below) total
values in the list, Median returns Null.
Slice(NSlices as Integer, SliceNumber as Integer) - Returns a Variant
containing the specified boundary value. This does quartiles, deciles,
or whatever you want - for the first quartile boundary value you would
invoke it as Slice(4,1). Median converts internally
Click to expand...
into
Slice(2,1).
If the number of values in the list divided by NSlices
Click to expand...
is
less than
than BucketSize (see below), Slice returns Null, as it does if
SliceNumber>= NSlices, or either argument is <1.

The externally visible Properties of the Class are:
Count - Returns a Long containing the number of values
Click to expand...
in
the list.
BucketSize - Gets or Sets the integer number of values
Click to expand...
in
a slice
below which Slice (or Median) will not return a value. This is
read/write and the default value is 3. The object of
Click to expand...
this
is to avoid
reporting stupid statistics (like the 4th decile
Click to expand...
boundary
of a list of
8 values!).

Let me know if you have any problems with it.


On Wed, 30 Jun 2004 17:39:35 -0700, "Meg"


I am trying to use the quartile function in a report
footer to calculate Q1, mediam and Q3 {=quartile
([field],1)}
When the report is opened, access prompts me for the
parameter value 'quartile'. The median function has the
same result, and the report will not preview the last page
of my report without freezing.

Access help states that 'you may need to install
msowcf.dll' but this is already in place.

Any other suggestions?

Thanks
Meg


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored- Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---
.
Click to expand...
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored- Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---
.
Click to expand...
 
You would get the error 91 if you were trying to use an uninitialised
Class instance, as I suggested. Differences between results on preview
and on printing usually result from something not being initialised
properly at the start of a new group. Are you sure that you are
following the details of my example (which doesn't show this problem)
carefully.

Moving code which accumulates data from OnPrint to OnFormat is _not_
advisable. When a group crosses a page boundary, the same Detail
record may be Formatted more than once.

One thought occurs to me: if you are using my statistical code, you
_must_ be running the Report in Detail rather than Summary mode. If
you don't want to see the detail, uncomment the commented lines in the
Format and Print Event code.

Error 91 is "object variable or with block variable not
set". I have had this before for a variety of reasons.
I seem to have got around that one now (dont know how!),
but am now experiencing a different issue.

When the report previews it gives me an incorrect median,
Q1 and Q3, but when it prints I get the correct values on
the printout!

I have moved the Detail_Print code to the OnFormat event
instead, and this seems to work....but would doing this
affect anything else?

Thanks!

-----Original Message-----
There should be no requirement to have the data grouped. Indeed, I
also use the Class in VBA contexts, outside Reports. In my example
database, if you are only interested in summary statistics over the
entire data set, you only need the code and controls that reference
the Class instances with names ending in Gbl. I wonder if you have a
variable naming problems - perhaps you are (e.g.) initialising one
Class instance and then trying to use another. Is there a description
for "Error 91"?

Gee...I dont feel very silly at all! Trying to use excel
functions in access without even realising! Weird that
its included in access help without mentioning that it
doesnt actually work in access.

Thanks very much for your help Peter and Sco.

Peter, I have downloaded your sample database, and have
used your code to display median, Q1 and Q3 in most of my
existing reports.
The only trouble I have is when I have a report that does
not include any grouping headers. When this is the case I
get error 91. Is there a requirement to have the data
grouped? If so I may need to force some artificail or
meaningless groupings.

Thanks again,
cheers,
Meg


-----Original Message-----
This, or a related question, comes up with monotonous
regularity,
since (as noted in another reply) Access does not have
native
functions for this class of statistics. Some time ago, I
created a
Class to provide the missing functionality. Rather than
upsetting
people by posting what would be an even longer message
and occupying
bandwidth unnecessarily, I have uploaded a zip file to my
business
website. The file contains the Class as a .cls file and a
small
database that demonstrates its use in a report.

The link is http://www.parish-data.com/data/cumstats.zip

The externally visible Methods of the Class are as
follows:
AddItem(VarValue as Variant) - Adds an value to the
internal list of
values of which the Median, first Decile, or whatever
boundary value
you want will be returned. If you pass AddItem a Null it
will simply
be ignored. The data type (Date, Integer, String, etc.)
of the first
value added to the list is recorded. Subsequent values
added must be
of the same type, or you will get an error box and the
new item will
not be added.
AddItems(VarArray as Variant | VarValue1,
VarValue2,....,VarValueN as
Variants) - Adds multiple values to the list. Accepts
either an Array
containing multiple values or a list of values. This
calls AddItem
internally, so it follows the same rules regarding data
types.
Median - Returns a Variant containing the median value
from the list.
This is written as a Method but can be regarded as a
readonly
Property. If there are less than 2 * BucketSize (see
below) total
values in the list, Median returns Null.
Slice(NSlices as Integer, SliceNumber as Integer) -
Returns a Variant
containing the specified boundary value. This does
quartiles, deciles,
or whatever you want - for the first quartile boundary
value you would
invoke it as Slice(4,1). Median converts internally into
Slice(2,1).
If the number of values in the list divided by NSlices is
less than
than BucketSize (see below), Slice returns Null, as it
does if
SliceNumber>= NSlices, or either argument is <1.

The externally visible Properties of the Class are:
Count - Returns a Long containing the number of values in
the list.
BucketSize - Gets or Sets the integer number of values in
a slice
below which Slice (or Median) will not return a value.
This is
read/write and the default value is 3. The object of this
is to avoid
reporting stupid statistics (like the 4th decile boundary
of a list of
8 values!).

Let me know if you have any problems with it.


On Wed, 30 Jun 2004 17:39:35 -0700, "Meg"


I am trying to use the quartile function in a report
footer to calculate Q1, mediam and Q3 {=quartile
([field],1)}
When the report is opened, access prompts me for the
parameter value 'quartile'. The median function has the
same result, and the report will not preview the last
page
of my report without freezing.

Access help states that 'you may need to install
msowcf.dll' but this is already in place.

Any other suggestions?

Thanks
Meg


Please respond to the Newsgroup, so that others may
benefit from the exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored-
Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the
World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total
Privacy via Encryption =---
.
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored- Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---
.
Click to expand...
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
So do I. Check for typos in your Class instance names!


I also notice that I get different results depending on
whether I display the median etc in group footers, or if I
only display an overall median in the report footer.
With the group footers, it is sometimes giving me a group
median that is outside of the range of values. (ie data
values ranging between 6.55 and 7.87, and median is
displayed as 8.17)

...think I may have done something wrong.


-----Original Message-----
There should be no requirement to have the data grouped. Indeed, I
also use the Class in VBA contexts, outside Reports. In my example
database, if you are only interested in summary statistics over the
entire data set, you only need the code and controls that reference
the Class instances with names ending in Gbl. I wonder if you have a
variable naming problems - perhaps you are (e.g.) initialising one
Class instance and then trying to use another. Is there a description
for "Error 91"?

Gee...I dont feel very silly at all! Trying to use excel
functions in access without even realising! Weird that
its included in access help without mentioning that it
doesnt actually work in access.

Thanks very much for your help Peter and Sco.

Peter, I have downloaded your sample database, and have
used your code to display median, Q1 and Q3 in most of my
existing reports.
The only trouble I have is when I have a report that does
not include any grouping headers. When this is the case I
get error 91. Is there a requirement to have the data
grouped? If so I may need to force some artificail or
meaningless groupings.

Thanks again,
cheers,
Meg


-----Original Message-----
This, or a related question, comes up with monotonous
regularity,
since (as noted in another reply) Access does not have
native
functions for this class of statistics. Some time ago, I
created a
Class to provide the missing functionality. Rather than
upsetting
people by posting what would be an even longer message
and occupying
bandwidth unnecessarily, I have uploaded a zip file to my
business
website. The file contains the Class as a .cls file and a
small
database that demonstrates its use in a report.

The link is http://www.parish-data.com/data/cumstats.zip

The externally visible Methods of the Class are as
follows:
AddItem(VarValue as Variant) - Adds an value to the
internal list of
values of which the Median, first Decile, or whatever
boundary value
you want will be returned. If you pass AddItem a Null it
will simply
be ignored. The data type (Date, Integer, String, etc.)
of the first
value added to the list is recorded. Subsequent values
added must be
of the same type, or you will get an error box and the
new item will
not be added.
AddItems(VarArray as Variant | VarValue1,
VarValue2,....,VarValueN as
Variants) - Adds multiple values to the list. Accepts
either an Array
containing multiple values or a list of values. This
calls AddItem
internally, so it follows the same rules regarding data
types.
Median - Returns a Variant containing the median value
from the list.
This is written as a Method but can be regarded as a
readonly
Property. If there are less than 2 * BucketSize (see
below) total
values in the list, Median returns Null.
Slice(NSlices as Integer, SliceNumber as Integer) -
Returns a Variant
containing the specified boundary value. This does
quartiles, deciles,
or whatever you want - for the first quartile boundary
value you would
invoke it as Slice(4,1). Median converts internally into
Slice(2,1).
If the number of values in the list divided by NSlices is
less than
than BucketSize (see below), Slice returns Null, as it
does if
SliceNumber>= NSlices, or either argument is <1.

The externally visible Properties of the Class are:
Count - Returns a Long containing the number of values in
the list.
BucketSize - Gets or Sets the integer number of values in
a slice
below which Slice (or Median) will not return a value.
This is
read/write and the default value is 3. The object of this
is to avoid
reporting stupid statistics (like the 4th decile boundary
of a list of
8 values!).

Let me know if you have any problems with it.


On Wed, 30 Jun 2004 17:39:35 -0700, "Meg"


I am trying to use the quartile function in a report
footer to calculate Q1, mediam and Q3 {=quartile
([field],1)}
When the report is opened, access prompts me for the
parameter value 'quartile'. The median function has the
same result, and the report will not preview the last
page
of my report without freezing.

Access help states that 'you may need to install
msowcf.dll' but this is already in place.

Any other suggestions?

Thanks
Meg


Please respond to the Newsgroup, so that others may
benefit from the exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored-
Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the
World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total
Privacy via Encryption =---
.
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher


----== Posted via Newsfeed.Com - Unlimited-Uncensored- Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---
.
Click to expand...
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Back
Top