By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

average with null values

P: n/a
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!

Oct 10 '07 #1
Share this Question
Share on Google+
10 Replies


P: n/a
On 10 Ott, 23:45, Toby Gallier <azr...@gmail.comwrote:
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!

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
---------------------------------------------------------------------------*------------------
Providing Access Users with the world's best Reporting Solution
http://www.datatime.eu/download.aspx

Oct 11 '07 #2

P: n/a
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" <az****@gmail.comwrote in message
news:11**********************@y42g2000hsy.googlegr oups.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!
Oct 11 '07 #3

P: n/a
On Oct 10, 9:39 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
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
CD********@FortuneJames.com

Oct 11 '07 #4

P: n/a
On Oct 10, 5:45 pm, Toby Gallier <azr...@gmail.comwrote:
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

Oct 11 '07 #5

P: n/a
On Oct 11, 7:49 pm, lyle <lyle.fairfi...@gmail.comwrote:
On Oct 10, 5:45 pm, Toby Gallier <azr...@gmail.comwrote:
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
CD********@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!

Oct 12 '07 #6

P: n/a
On Oct 12, 3:20 pm, CDMAPos...@FortuneJames.com wrote:
On Oct 11, 7:49 pm, lyle <lyle.fairfi...@gmail.comwrote:
On Oct 10, 5:45 pm, Toby Gallier <azr...@gmail.comwrote:
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.

Oct 12 '07 #7

P: n/a
On Oct 12, 4:03 pm, lyle <lyle.fairfi...@gmail.comwrote:
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
CD********@FortuneJames.com

Oct 12 '07 #8

P: n/a
On Oct 12, 4:03 pm, lyle <lyle.fairfi...@gmail.comwrote:
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
CD********@FortuneJames.com

Oct 12 '07 #9

P: n/a
CD********@FortuneJames.com wrote in news:1192224245.970721.48060
@v29g2000prd.googlegroups.com:
On Oct 12, 4:03 pm, lyle <lyle.fairfi...@gmail.comwrote:
>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
CD********@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).Val ue
I think it is a useful and valid VBA/DAO construction.
--
lyle fairfield
Oct 12 '07 #10

P: n/a
On Oct 12, 5:40 pm, lyle fairfield <lylef...@yahoo.cawrote:
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).Val ue
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).Val ue in mind for the
future.

James A. Fortune
CD********@FortuneJames.com

Oct 17 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.