average with null values

Hello!

I have a form that is calculating averages as follows:

" =(NZ([value1])+Nz([value2])+Nz([value]))/3 "

However I need to now adjust for null values , so for example if
value2 is null I would then need to base my average on just 2 values
instead of 3 i am currently using in my string. How can i have the
form update the "3" based on the number of values that are populated?

Thanks!

Re: average with null values

On 10 Ott, 23:45, Toby Gallier wrote:
> Hello!
>
> I have a form that is calculating averages as follows:
>
> " =3D(NZ([value1])+Nz([value2])+Nz([value]))/3 "
>
> However I need to now adjust for null values , so for example if
> value2 is null I would then need to base my average on just 2 values
> instead of 3 i am currently using in my string. How can i have the
> form update the "3" based on the number of values that are populated?
>
> Thanks!


How about replacing the 3 (in the denominator) with a sum of three
IIFs
taking 0 when the value is NULL or any invalid number,
and 1 otherwise ?

watch for line breaks:

IIF( ISNULL(Value1) and ISNULL(Value2) and ISNULL(Value3), 0,
( NZ(value1) + NZ(value2) +NZ( value3) ) / (IIF(ISNULL(Value1),0,1)
+ IIF(ISNULL(Value2),0,1) + IIF(ISNULL(Value3),0,1)))

[includes an additional check for the pathological case of 3 nulls]


-P
------------------------------------------------------------ ---------------=
=AD------------------
Providing Access Users with the world's best Reporting Solution
http://www.datatime.eu/download.aspx

Re: average with null values

There are several traps here, incluing the possibility of division by zero
if all 3 fields are null.

Something like this (untested) expression should work:

IIf( [value1] Is Null And [value2] Is Null And [value3] Is Null, Null,
(IIf([value1] Is Null, 0, [value1])
+ IIf([value2] Is Null, 0, [value2])
+ IIf([value3] Is Null, 0, [value3]))
/ - (([value1] Is Not Null)
+ ([value2] Is Not Null)
+ ([value3] Is Not Null)))

There a no VBA function calls there, such as Nz() or IsNull(). (JET has a
native IIf() function.) So that should give the best performance, and should
not mess up the data type (the way Nz() can.) You may need to adjust the
brackets.

The expression in the denominator relies on the fact that Access uses -1 for
True.

The fact that you are averaging across fields may mean the table is not
normalized correctly. If you have repeating fields (such as value1, value2,
value3, ...), there's a very good chance that you should have many *records*
in a related table, instead of many fields on this table. It would then be
child's play to average those fields (using a Total query.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Toby Gallier" wrote in message
news:1192052756.213366.253170@y42g2000hsy.googlegroups.com.. .
> Hello!
>
> I have a form that is calculating averages as follows:
>
> " =(NZ([value1])+Nz([value2])+Nz([value]))/3 "
>
> However I need to now adjust for null values , so for example if
> value2 is null I would then need to base my average on just 2 values
> instead of 3 i am currently using in my string. How can i have the
> form update the "3" based on the number of values that are populated?
>
> Thanks!

Re: average with null values

On Oct 10, 9:39 pm, "Allen Browne" wrote:
> There are several traps here, incluing the possibility of division by zero
> if all 3 fields are null.
>
> Something like this (untested) expression should work:
>
> IIf( [value1] Is Null And [value2] Is Null And [value3] Is Null, Null,
> (IIf([value1] Is Null, 0, [value1])
> + IIf([value2] Is Null, 0, [value2])
> + IIf([value3] Is Null, 0, [value3]))
> / - (([value1] Is Not Null)
> + ([value2] Is Not Null)
> + ([value3] Is Not Null)))
>
> There a no VBA function calls there, such as Nz() or IsNull(). (JET has a
> native IIf() function.) So that should give the best performance, and should
> not mess up the data type (the way Nz() can.) You may need to adjust the
> brackets.

Nice idea. Did anyone do any performance tests?

>
> The expression in the denominator relies on the fact that Access uses -1 for
> True.

You lose the reliance at no extra cost using:

/ (IIf([value1] IS NULL, 0, 1) + IIf([value2] IS NULL, 0, 1) +
IIf([value2] IS NULL, 0, 1))

>
> The fact that you are averaging across fields may mean the table is not
> normalized correctly. If you have repeating fields (such as value1, value2,
> value3, ...), there's a very good chance that you should have many *records*
> in a related table, instead of many fields on this table. It would then be
> child's play to average those fields (using a Total query.)

I agree with your warning. The fact that the values are similar
enough to be averaged together makes the possibility even more likely.

James A. Fortune
CDMAPoster@FortuneJames.com

Re: average with null values

On Oct 10, 5:45 pm, Toby Gallier wrote:
> Hello!
>
> I have a form that is calculating averages as follows:
>
> " =(NZ([value1])+Nz([value2])+Nz([value]))/3 "
>
> However I need to now adjust for null values , so for example if
> value2 is null I would then need to base my average on just 2 values
> instead of 3 i am currently using in my string. How can i have the
> form update the "3" based on the number of values that are populated?
>
> Thanks!

Whimsical Air Code


Public Function MyAvg(ParamArray rValues())
Dim s$
Dim z&
For z = 0 To UBound(rValues)
s = s & "SELECT TOP 1 " & Nz(rValues(z), "null") & " AS Temp
FROM MSysObjects"
If z <> UBound(rValues) Then _
s = s & vbNewLine & "UNION ALL" & vbNewLine
Next z
s = s & "]. sq"
s = "SELECT AVG(sq.Temp) FROM " & vbNewLine & "[" & s
MyAvg = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
End Function

Sub temp()
Dim v(0 To 2) As Variant
v(0) = 5
v(1) = Null
v(2) = 10
Debug.Print MyAvg(v(0), v(1), v(2)) ' 7.5
End Sub

Re: average with null values

On Oct 11, 7:49 pm, lyle wrote:
> On Oct 10, 5:45 pm, Toby Gallier wrote:
>
> > Hello!
>
> > I have a form that is calculating averages as follows:
>
> > " =(NZ([value1])+Nz([value2])+Nz([value]))/3 "
>
> > However I need to now adjust for null values , so for example if
> > value2 is null I would then need to base my average on just 2 values
> > instead of 3 i am currently using in my string. How can i have the
> > form update the "3" based on the number of values that are populated?
>
> > Thanks!
>
> Whimsical Air Code
>
> Public Function MyAvg(ParamArray rValues())
> Dim s$
> Dim z&
> For z = 0 To UBound(rValues)
> s = s & "SELECT TOP 1 " & Nz(rValues(z), "null") & " AS Temp
> FROM MSysObjects"
> If z <> UBound(rValues) Then _
> s = s & vbNewLine & "UNION ALL" & vbNewLine
> Next z
> s = s & "]. sq"
> s = "SELECT AVG(sq.Temp) FROM " & vbNewLine & "[" & s
> MyAvg = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
> End Function
>
> Sub temp()
> Dim v(0 To 2) As Variant
> v(0) = 5
> v(1) = Null
> v(2) = 10
> Debug.Print MyAvg(v(0), v(1), v(2)) ' 7.5
> End Sub

I realize that attempting to improve this gem is like gilding the lily
or telling Picasso that you'll fix up his painting to give it some
needed realism. For the interest of novices, how about (untested):

Public Function MyAvg(ParamArray rValues()) As Variant
Dim dblSum As Double
Dim lngI As Long

dblSum = CDbl(0)
lngI = 0
For lngI = 0 To UBound(rValues)
If Not IsNull(rValues(lngI)) Then
dblSum = dblSum + rValues(lngI)
lngI = lngI + 1
End If
Next lngI
MyAvg = Null
If lngI = 0 Then Exit Function
MyAvg = dblSum / lngI
End Function

James A. Fortune
CDMAPoster@FortuneJames.com

The most horrible examples of his machines have an anticipation
factor, as the machine makes slow but steady progress toward its goal.
-- http://en.wikipedia.org/wiki/Rube_Goldberg

About five months ago my friend Jamie asked me to help him move some
of his boss' paintings from one climate controlled location to
another. I was carrying one wrapped about with special wrapping
material. I glanced at the painting Jamie was carrying and quietly
recognized it from my Art History class. Are these originals or
copies? Originals. Then please don't tell me which painting I'm
carrying!

Re: average with null values

On Oct 12, 3:20 pm, CDMAPos...@FortuneJames.com wrote:
> On Oct 11, 7:49 pm, lyle wrote:
>
>
>
> > On Oct 10, 5:45 pm, Toby Gallier wrote:
>
> > > Hello!
>
> > > I have a form that is calculating averages as follows:
>
> > > " =(NZ([value1])+Nz([value2])+Nz([value]))/3 "
>
> > > However I need to now adjust for null values , so for example if
> > > value2 is null I would then need to base my average on just 2 values
> > > instead of 3 i am currently using in my string. How can i have the
> > > form update the "3" based on the number of values that are populated?
>
> > > Thanks!
>
> > Whimsical Air Code
>
> > Public Function MyAvg(ParamArray rValues())
> > Dim s$
> > Dim z&
> > For z = 0 To UBound(rValues)
> > s = s & "SELECT TOP 1 " & Nz(rValues(z), "null") & " AS Temp
> > FROM MSysObjects"
> > If z <> UBound(rValues) Then _
> > s = s & vbNewLine & "UNION ALL" & vbNewLine
> > Next z
> > s = s & "]. sq"
> > s = "SELECT AVG(sq.Temp) FROM " & vbNewLine & "[" & s
> > MyAvg = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
> > End Function
>
> > Sub temp()
> > Dim v(0 To 2) As Variant
> > v(0) = 5
> > v(1) = Null
> > v(2) = 10
> > Debug.Print MyAvg(v(0), v(1), v(2)) ' 7.5
> > End Sub
>
> I realize that attempting to improve this gem is like gilding the lily
> or telling Picasso that you'll fix up his painting to give it some
> needed realism. For the interest of novices, how about (untested):
>
> Public Function MyAvg(ParamArray rValues()) As Variant
> Dim dblSum As Double
> Dim lngI As Long
>
> dblSum = CDbl(0)
> lngI = 0
> For lngI = 0 To UBound(rValues)
> If Not IsNull(rValues(lngI)) Then
> dblSum = dblSum + rValues(lngI)
> lngI = lngI + 1
> End If
> Next lngI
> MyAvg = Null
> If lngI = 0 Then Exit Function
> MyAvg = dblSum / lngI
> End Function
>
> James A. Fortune
> CDMAPos...@FortuneJames.com
>
> The most horrible examples of his machines have an anticipation
> factor, as the machine makes slow but steady progress toward its goal.
> --http://en.wikipedia.org/wiki/Rube_Goldberg
>
> About five months ago my friend Jamie asked me to help him move some
> of his boss' paintings from one climate controlled location to
> another. I was carrying one wrapped about with special wrapping
> material. I glanced at the painting Jamie was carrying and quietly
> recognized it from my Art History class. Are these originals or
> copies? Originals. Then please don't tell me which painting I'm
> carrying!

I was toying with the idea of writing a series of functions that
emulated JET's aggregate functions with arrays of values. While they
might not be so efficient, they would be likely to give the same
answers that SQL does, and there could be a benefit to that.

Prototypes ....

Public Function aAvg(ParamArray rValues() As Variant)
Dim s$
Dim z&
For z = LBound(rValues) To UBound(rValues)
If IsMissing(rValues(z)) Then rValues(z) = Null
s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
FROM MSysObjects"
If z <> UBound(rValues) Then _
s = s & vbNewLine & "UNION ALL" & vbNewLine
Next z
s = s & "]. sq"
s = "SELECT Avg(sq.Temp) FROM " & vbNewLine & "[" & s
aAvg = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
End Function

Public Function aCount(ParamArray rValues() As Variant)
Dim s$
Dim z&
For z = LBound(rValues) To UBound(rValues)
If IsMissing(rValues(z)) Then rValues(z) = Null
s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
FROM MSysObjects"
If z <> UBound(rValues) Then _
s = s & vbNewLine & "UNION ALL" & vbNewLine
Next z
s = s & "]. sq"
s = "SELECT Count(sq.Temp) FROM " & vbNewLine & "[" & s
aCount = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
End Function

Public Function aMax(ParamArray rValues())
Dim s$
Dim z&
For z = LBound(rValues) To UBound(rValues)
If IsMissing(rValues(z)) Then rValues(z) = Null
s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
FROM MSysObjects"
If z <> UBound(rValues) Then _
s = s & vbNewLine & "UNION ALL" & vbNewLine
Next z
s = s & "]. sq"
s = "SELECT Max(sq.Temp) FROM " & vbNewLine & "[" & s
aMax = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
End Function

Public Function aMin(ParamArray rValues())
Dim s$
Dim z&
For z = LBound(rValues) To UBound(rValues)
If IsMissing(rValues(z)) Then rValues(z) = Null
s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
FROM MSysObjects"
If z <> UBound(rValues) Then _
s = s & vbNewLine & "UNION ALL" & vbNewLine
Next z
s = s & "]. sq"
s = "SELECT Min(sq.Temp) FROM " & vbNewLine & "[" & s
aMin = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
End Function

Public Function aSum(ParamArray rValues())
Dim s$
Dim z&
For z = LBound(rValues) To UBound(rValues)
If IsMissing(rValues(z)) Then rValues(z) = Null
s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
FROM MSysObjects"
If z <> UBound(rValues) Then _
s = s & vbNewLine & "UNION ALL" & vbNewLine
Next z
s = s & "]. sq"
s = "SELECT Sum(sq.Temp) FROM " & vbNewLine & "[" & s
aSum = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
End Function

It would be easy, of course, to rewrite these to accept a variant
array, instead of a parameter array, or even to examine what's passed
and process the values appropriately.

Re: average with null values

On Oct 12, 4:03 pm, lyle wrote:
> I was toying with the idea of writing a series of functions that
> emulated JET's aggregate functions with arrays of values. While they
> might not be so efficient, they would be likely to give the same
> answers that SQL does, and there could be a benefit to that.
>
> Prototypes ....
>
> Public Function aAvg(ParamArray rValues() As Variant)
> Dim s$
> Dim z&
> For z = LBound(rValues) To UBound(rValues)
> If IsMissing(rValues(z)) Then rValues(z) = Null
> s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
> FROM MSysObjects"
> If z <> UBound(rValues) Then _
> s = s & vbNewLine & "UNION ALL" & vbNewLine
> Next z
> s = s & "]. sq"
> s = "SELECT Avg(sq.Temp) FROM " & vbNewLine & "[" & s
> aAvg = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
> End Function
>
> Public Function aCount(ParamArray rValues() As Variant)
> Dim s$
> Dim z&
> For z = LBound(rValues) To UBound(rValues)
> If IsMissing(rValues(z)) Then rValues(z) = Null
> s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
> FROM MSysObjects"
> If z <> UBound(rValues) Then _
> s = s & vbNewLine & "UNION ALL" & vbNewLine
> Next z
> s = s & "]. sq"
> s = "SELECT Count(sq.Temp) FROM " & vbNewLine & "[" & s
> aCount = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
> End Function
>
> Public Function aMax(ParamArray rValues())
> Dim s$
> Dim z&
> For z = LBound(rValues) To UBound(rValues)
> If IsMissing(rValues(z)) Then rValues(z) = Null
> s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
> FROM MSysObjects"
> If z <> UBound(rValues) Then _
> s = s & vbNewLine & "UNION ALL" & vbNewLine
> Next z
> s = s & "]. sq"
> s = "SELECT Max(sq.Temp) FROM " & vbNewLine & "[" & s
> aMax = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
> End Function
>
> Public Function aMin(ParamArray rValues())
> Dim s$
> Dim z&
> For z = LBound(rValues) To UBound(rValues)
> If IsMissing(rValues(z)) Then rValues(z) = Null
> s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
> FROM MSysObjects"
> If z <> UBound(rValues) Then _
> s = s & vbNewLine & "UNION ALL" & vbNewLine
> Next z
> s = s & "]. sq"
> s = "SELECT Min(sq.Temp) FROM " & vbNewLine & "[" & s
> aMin = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
> End Function
>
> Public Function aSum(ParamArray rValues())
> Dim s$
> Dim z&
> For z = LBound(rValues) To UBound(rValues)
> If IsMissing(rValues(z)) Then rValues(z) = Null
> s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
> FROM MSysObjects"
> If z <> UBound(rValues) Then _
> s = s & vbNewLine & "UNION ALL" & vbNewLine
> Next z
> s = s & "]. sq"
> s = "SELECT Sum(sq.Temp) FROM " & vbNewLine & "[" & s
> aSum = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
> End Function
>
> It would be easy, of course, to rewrite these to accept a variant
> array, instead of a parameter array, or even to examine what's passed
> and process the values appropriately

I didn't mean to sound so harsh about your function. It was
beneficial even without it's history as an emulation of JET's
aggregate functions. Plus, the emulation functions look to be useful
in their own right for testing purposes. I'm sorry I made you feel
you had to go to such lengths to justify it. I sincerely enjoyed the
function you wrote, especially in how easy it is to add additional
fields from the same record, e.g., ([value1], [value2], [value3],
[value4]). It also showed how avg() would be used for the normalized
case. It even used Nz() in its native VBA context. My main criticism
was that a beginner would have trouble understanding it. To me, it
was a masterpiece. The performance might be a little slow, but I
didn't check and I doubt that it would be much of a problem anyway.
The delight I got from going over it more than made up for a couple of
peccadillos. The Rube Goldberg quote was more from the way in which
the SQL string was ultimately tied together rather than from
unnecessary complications that would slow down performance.

James A. Fortune
CDMAPoster@FortuneJames.com

Re: average with null values

On Oct 12, 4:03 pm, lyle wrote:

> Prototypes ....
>
> Public Function aAvg(ParamArray rValues() As Variant)
> Dim s$
> Dim z&
> For z = LBound(rValues) To UBound(rValues)
> If IsMissing(rValues(z)) Then rValues(z) = Null
> s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
> FROM MSysObjects"
> If z <> UBound(rValues) Then _
> s = s & vbNewLine & "UNION ALL" & vbNewLine
> Next z
> s = s & "]. sq"
> s = "SELECT Avg(sq.Temp) FROM " & vbNewLine & "[" & s
> aAvg = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
> End Function
>
> Public Function aCount(ParamArray rValues() As Variant)
> Dim s$
> Dim z&
> For z = LBound(rValues) To UBound(rValues)
> If IsMissing(rValues(z)) Then rValues(z) = Null
> s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
> FROM MSysObjects"
> If z <> UBound(rValues) Then _
> s = s & vbNewLine & "UNION ALL" & vbNewLine
> Next z
> s = s & "]. sq"
> s = "SELECT Count(sq.Temp) FROM " & vbNewLine & "[" & s
> aCount = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
> End Function
>
> Public Function aMax(ParamArray rValues())
> Dim s$
> Dim z&
> For z = LBound(rValues) To UBound(rValues)
> If IsMissing(rValues(z)) Then rValues(z) = Null
> s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
> FROM MSysObjects"
> If z <> UBound(rValues) Then _
> s = s & vbNewLine & "UNION ALL" & vbNewLine
> Next z
> s = s & "]. sq"
> s = "SELECT Max(sq.Temp) FROM " & vbNewLine & "[" & s
> aMax = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
> End Function
>
> Public Function aMin(ParamArray rValues())
> Dim s$
> Dim z&
> For z = LBound(rValues) To UBound(rValues)
> If IsMissing(rValues(z)) Then rValues(z) = Null
> s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
> FROM MSysObjects"
> If z <> UBound(rValues) Then _
> s = s & vbNewLine & "UNION ALL" & vbNewLine
> Next z
> s = s & "]. sq"
> s = "SELECT Min(sq.Temp) FROM " & vbNewLine & "[" & s
> aMin = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
> End Function
>
> Public Function aSum(ParamArray rValues())
> Dim s$
> Dim z&
> For z = LBound(rValues) To UBound(rValues)
> If IsMissing(rValues(z)) Then rValues(z) = Null
> s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
> FROM MSysObjects"
> If z <> UBound(rValues) Then _
> s = s & vbNewLine & "UNION ALL" & vbNewLine
> Next z
> s = s & "]. sq"
> s = "SELECT Sum(sq.Temp) FROM " & vbNewLine & "[" & s
> aSum = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
> End Function

I have a question about your emulation functions. The functions look
like they were designed to take advantage of how ADODB's recordsets
are represented. Was the original impetus for use with ADODB
connections?

James A. Fortune
CDMAPoster@FortuneJames.com

Re: average with null values

CDMAPoster@FortuneJames.com wrote in news:1192224245.970721.48060
@v29g2000prd.googlegroups.com:

> On Oct 12, 4:03 pm, lyle wrote:
>
>> Prototypes ....
>>
>> Public Function aAvg(ParamArray rValues() As Variant)
>> Dim s$
>> Dim z&
>> For z = LBound(rValues) To UBound(rValues)
>> If IsMissing(rValues(z)) Then rValues(z) = Null
>> s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
>> FROM MSysObjects"
>> If z <> UBound(rValues) Then _
>> s = s & vbNewLine & "UNION ALL" & vbNewLine
>> Next z
>> s = s & "]. sq"
>> s = "SELECT Avg(sq.Temp) FROM " & vbNewLine & "[" & s
>> aAvg = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
>> End Function
>>
>> Public Function aCount(ParamArray rValues() As Variant)
>> Dim s$
>> Dim z&
>> For z = LBound(rValues) To UBound(rValues)
>> If IsMissing(rValues(z)) Then rValues(z) = Null
>> s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
>> FROM MSysObjects"
>> If z <> UBound(rValues) Then _
>> s = s & vbNewLine & "UNION ALL" & vbNewLine
>> Next z
>> s = s & "]. sq"
>> s = "SELECT Count(sq.Temp) FROM " & vbNewLine & "[" & s
>> aCount = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
>> End Function
>>
>> Public Function aMax(ParamArray rValues())
>> Dim s$
>> Dim z&
>> For z = LBound(rValues) To UBound(rValues)
>> If IsMissing(rValues(z)) Then rValues(z) = Null
>> s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
>> FROM MSysObjects"
>> If z <> UBound(rValues) Then _
>> s = s & vbNewLine & "UNION ALL" & vbNewLine
>> Next z
>> s = s & "]. sq"
>> s = "SELECT Max(sq.Temp) FROM " & vbNewLine & "[" & s
>> aMax = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
>> End Function
>>
>> Public Function aMin(ParamArray rValues())
>> Dim s$
>> Dim z&
>> For z = LBound(rValues) To UBound(rValues)
>> If IsMissing(rValues(z)) Then rValues(z) = Null
>> s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
>> FROM MSysObjects"
>> If z <> UBound(rValues) Then _
>> s = s & vbNewLine & "UNION ALL" & vbNewLine
>> Next z
>> s = s & "]. sq"
>> s = "SELECT Min(sq.Temp) FROM " & vbNewLine & "[" & s
>> aMin = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
>> End Function
>>
>> Public Function aSum(ParamArray rValues())
>> Dim s$
>> Dim z&
>> For z = LBound(rValues) To UBound(rValues)
>> If IsMissing(rValues(z)) Then rValues(z) = Null
>> s = s & "SELECT TOP 1 " & Nz(rValues(z), "Null") & " AS Temp
>> FROM MSysObjects"
>> If z <> UBound(rValues) Then _
>> s = s & vbNewLine & "UNION ALL" & vbNewLine
>> Next z
>> s = s & "]. sq"
>> s = "SELECT Sum(sq.Temp) FROM " & vbNewLine & "[" & s
>> aSum = DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
>> End Function
>
> I have a question about your emulation functions. The functions look
> like they were designed to take advantage of how ADODB's recordsets
> are represented. Was the original impetus for use with ADODB
> connections?
>
> James A. Fortune
> CDMAPoster@FortuneJames.com

I am a fan of ADODB but I didn'think about it this time. I just cast
about for a way to answer the OP's post without keeping track of the
number of non-null values explicitly. While doing so I stumbled upon the
notion of emulating and using the domain aggregate functions by creating
a pseudo table as a sub query (string).
Pending more thought and experimentation, I like it.

While
DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
may be more commonly manifested in ADO as
CurrentProject.Connection.Execute(s).Fields(0).Value
I think it is a useful and valid VBA/DAO construction.


--
lyle fairfield

Re: average with null values

On Oct 12, 5:40 pm, lyle fairfield wrote:

> I am a fan of ADODB but I didn'think about it this time. I just cast
> about for a way to answer the OP's post without keeping track of the
> number of non-null values explicitly. While doing so I stumbled upon the
> notion of emulating and using the domain aggregate functions by creating
> a pseudo table as a sub query (string).
> Pending more thought and experimentation, I like it.
>
> While
> DBEngine(0)(0).OpenRecordset(s).Fields(0).Value
> may be more commonly manifested in ADO as
> CurrentProject.Connection.Execute(s).Fields(0).Value
> I think it is a useful and valid VBA/DAO construction.

Lyle,

Thanks for those insights. With the domain aggregate functions being
so easy to emulate and with functions like DCount and DSum being just
a special case of DLookup (not hard to emulate either) and with many
VBA functions calling simple API equivalents, it makes you wonder if
bound forms ARE Access' main claim to fame :-). I still love Access,
but I'm starting to hear the clarion of java and C# -- maybe Spry
also. Of course, Access RAD demos will convey the concept. I'll keep
CurrentProject.Connection.Execute(s).Fields(0).Value in mind for the
future.

James A. Fortune
CDMAPoster@FortuneJames.com