473,322 Members | 1,718 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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!

Oct 10 '07 #1
10 8967
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Stephen Miller | last post by:
Hi, I am trying to add a staggered running total and average to a query returning quarterly CPI data. I need to add 4 quarterly data points together to calculate a moving 12-month sum (YrCPI),...
8
by: Alex | last post by:
My table is laid out as such: ID (int) What (varchar 20) TimeStamp (smalldatetime) ------- ------------- --------------- 73 Start ...
3
by: CSDunn | last post by:
Hello, I have 14 fields on a report that hold integer values. The field names use the following naming convention: T1Number, T2Number ....T14Number. I need to get a 'sub total' of all fields as...
3
by: satish | last post by:
create table employee(empid int,empname varchar(20),managerid int not null, sal int) insert into employee values(1,'ranga',22,5000) insert into employee values(2,'satish',22,8000) insert into...
2
by: melisimp | last post by:
I am trying to select the following averages from the tables below. average order turn time in minutes (completion_time - order_time), average fee of the final invoice, and the average # of invoices...
10
by: blackflicker | last post by:
Hello, I have a table which is: DROP TABLE IF EXISTS dummy; CREATE TABLE dummy ( id int(11) not null auto_increment, entered int(11) not null default 0, primary key(id) ); And dummy...
2
by: ReneHernandez | last post by:
Good afternoon, I am trying to calculate an average over multiple fields. I've tried using the following =Avg(IIF( = "NA",Null, Val())) + Avg(IIF( = "NA",Null, Val())) + Avg(IIF( = "NA",Null,...
0
by: SuzK | last post by:
I am trying to calculate in VBA in Access 2002 a moving average and update a table with the calculations. Fields in my WeeklyData table are Week Ending (date) ItemNbr (double) Sales Dollars...
21
by: Bill Cunningham | last post by:
I have create these 2 files. Called main.c and atr.c. They seem to work pretty well. I just wanted to submit them to see what if any errors others that know more might find. Thanks. atr.c ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.