G
Guest
Is there a function I can use like "Join(fieldname(), ", ") that will join
numbers instead of strings into an array?
numbers instead of strings into an array?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
RobFMS
How is this data stored? In ADO, there is a method called .GetRows (I
believe that's the correct name). This stuffs the data into an array.
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
FMS Advanced Systems Group
http://www.fmsasg.com/
believe that's the correct name). This stuffs the data into an array.
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
FMS Advanced Systems Group
http://www.fmsasg.com/
G
Guest
I'm not sure how to use that function. Could I somehow convert my code to use
the .GetRows function? Here's my code
Function GetNumbers()
Dim db As Database
Dim Rst As Recordset
Dim MyNum As Double, myString, MyArray
Set db = CurrentDb()
Set Rst = db.OpenRecordset("ProjectsAging")
If Rst.RecordCount = 0 Then
Exit Function
End If
Rst.MoveFirst
Do While Not Rst.EOF
MyNum = Rst![DaysToPay]
Rst.MoveNext
myString = myString & MyNum & ", "
Loop
MyArray = Split(myString, ",", -1)
Numbers = HarmonicMean(MyArray)
Set Rst = Nothing
Set db = Nothing
End Function
This provides a "string" in the correct format..but of course it does me no
good if I can't run my HarmonicMean function on it...
the .GetRows function? Here's my code
Function GetNumbers()
Dim db As Database
Dim Rst As Recordset
Dim MyNum As Double, myString, MyArray
Set db = CurrentDb()
Set Rst = db.OpenRecordset("ProjectsAging")
If Rst.RecordCount = 0 Then
Exit Function
End If
Rst.MoveFirst
Do While Not Rst.EOF
MyNum = Rst![DaysToPay]
Rst.MoveNext
myString = myString & MyNum & ", "
Loop
MyArray = Split(myString, ",", -1)
Numbers = HarmonicMean(MyArray)
Set Rst = Nothing
Set db = Nothing
End Function
This provides a "string" in the correct format..but of course it does me no
good if I can't run my HarmonicMean function on it...
R
RobFMS
Cydney
I will take a look at this shortly. Just letting you know that I have not
forgotten.
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
I will take a look at this shortly. Just letting you know that I have not
forgotten.
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
Cydney said:I'm not sure how to use that function. Could I somehow convert my code to
use
the .GetRows function? Here's my code
Function GetNumbers()
Dim db As Database
Dim Rst As Recordset
Dim MyNum As Double, myString, MyArray
Set db = CurrentDb()
Set Rst = db.OpenRecordset("ProjectsAging")
If Rst.RecordCount = 0 Then
Exit Function
End If
Rst.MoveFirst
Do While Not Rst.EOF
MyNum = Rst![DaysToPay]
Rst.MoveNext
myString = myString & MyNum & ", "
Loop
MyArray = Split(myString, ",", -1)
Numbers = HarmonicMean(MyArray)
Set Rst = Nothing
Set db = Nothing
End Function
This provides a "string" in the correct format..but of course it does me
no
good if I can't run my HarmonicMean function on it...
--
THX cs
RobFMS said:How is this data stored? In ADO, there is a method called .GetRows (I
believe that's the correct name). This stuffs the data into an array.
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
FMS Advanced Systems Group
http://www.fmsasg.com/
R
RobFMS
I've kept it in DAO so that you can manage it easier.
Its untested b/c I do not have the table/query you have but it should be
well enough for you to work with.
Let me know if you have any other questions.
Rob Mastrostefano
Public Sub TestRun()
' Define the array for the values to be put into
Dim MyArray() As String
' Pass the array into the subprocedure. The subprocedure will populate
this array
' and return it back to you. This happens b/c you are setting the
parameter as a
' ByRef value.
GetNumbers (MyArray)
End Sub
Public Sub GetNumbers( _
ByRef arrNumbers() As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim arrTemp() As String
Dim intIndex As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ProjectsAging")
If (rst.BOF And rst.EOF) Then
' no records to process
Else
' has records to process
intIndex = 0
Do While Not rst.EOF
ReDim Preserve arrTemp(intIndex)
arrTemp(intIndex) = rst("DaysToPay")
intIndex = intIndex + 1
rst.MoveNext
Loop
End If
Set rst = Nothing
Set dbs = Nothing
End Sub
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
Its untested b/c I do not have the table/query you have but it should be
well enough for you to work with.
Let me know if you have any other questions.
Rob Mastrostefano
Public Sub TestRun()
' Define the array for the values to be put into
Dim MyArray() As String
' Pass the array into the subprocedure. The subprocedure will populate
this array
' and return it back to you. This happens b/c you are setting the
parameter as a
' ByRef value.
GetNumbers (MyArray)
End Sub
Public Sub GetNumbers( _
ByRef arrNumbers() As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim arrTemp() As String
Dim intIndex As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ProjectsAging")
If (rst.BOF And rst.EOF) Then
' no records to process
Else
' has records to process
intIndex = 0
Do While Not rst.EOF
ReDim Preserve arrTemp(intIndex)
arrTemp(intIndex) = rst("DaysToPay")
intIndex = intIndex + 1
rst.MoveNext
Loop
End If
Set rst = Nothing
Set dbs = Nothing
End Sub
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
Cydney said:I'm not sure how to use that function. Could I somehow convert my code to
use
the .GetRows function? Here's my code
Function GetNumbers()
Dim db As Database
Dim Rst As Recordset
Dim MyNum As Double, myString, MyArray
Set db = CurrentDb()
Set Rst = db.OpenRecordset("ProjectsAging")
If Rst.RecordCount = 0 Then
Exit Function
End If
Rst.MoveFirst
Do While Not Rst.EOF
MyNum = Rst![DaysToPay]
Rst.MoveNext
myString = myString & MyNum & ", "
Loop
MyArray = Split(myString, ",", -1)
Numbers = HarmonicMean(MyArray)
Set Rst = Nothing
Set db = Nothing
End Function
This provides a "string" in the correct format..but of course it does me
no
good if I can't run my HarmonicMean function on it...
--
THX cs
RobFMS said:How is this data stored? In ADO, there is a method called .GetRows (I
believe that's the correct name). This stuffs the data into an array.
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
FMS Advanced Systems Group
http://www.fmsasg.com/
G
Guest
Got a problem..
Error says: "Type mismatch: array or user-defined type expected"
Am I missing something?
--
THX cs
Error says: "Type mismatch: array or user-defined type expected"
Am I missing something?
--
THX cs
RobFMS said:I've kept it in DAO so that you can manage it easier.
Its untested b/c I do not have the table/query you have but it should be
well enough for you to work with.
Let me know if you have any other questions.
Rob Mastrostefano
Public Sub TestRun()
' Define the array for the values to be put into
Dim MyArray() As String
' Pass the array into the subprocedure. The subprocedure will populate
this array
' and return it back to you. This happens b/c you are setting the
parameter as a
' ByRef value.
GetNumbers (MyArray)
End Sub
Public Sub GetNumbers( _
ByRef arrNumbers() As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim arrTemp() As String
Dim intIndex As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ProjectsAging")
If (rst.BOF And rst.EOF) Then
' no records to process
Else
' has records to process
intIndex = 0
Do While Not rst.EOF
ReDim Preserve arrTemp(intIndex)
arrTemp(intIndex) = rst("DaysToPay")
intIndex = intIndex + 1
rst.MoveNext
Loop
End If
Set rst = Nothing
Set dbs = Nothing
End Sub
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
Cydney said:I'm not sure how to use that function. Could I somehow convert my code to
use
the .GetRows function? Here's my code
Function GetNumbers()
Dim db As Database
Dim Rst As Recordset
Dim MyNum As Double, myString, MyArray
Set db = CurrentDb()
Set Rst = db.OpenRecordset("ProjectsAging")
If Rst.RecordCount = 0 Then
Exit Function
End If
Rst.MoveFirst
Do While Not Rst.EOF
MyNum = Rst![DaysToPay]
Rst.MoveNext
myString = myString & MyNum & ", "
Loop
MyArray = Split(myString, ",", -1)
Numbers = HarmonicMean(MyArray)
Set Rst = Nothing
Set db = Nothing
End Function
This provides a "string" in the correct format..but of course it does me
no
good if I can't run my HarmonicMean function on it...
--
THX cs
RobFMS said:How is this data stored? In ADO, there is a method called .GetRows (I
believe that's the correct name). This stuffs the data into an array.
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
FMS Advanced Systems Group
http://www.fmsasg.com/
Is there a function I can use like "Join(fieldname(), ", ") that will
join
numbers instead of strings into an array?
G
Guest
Here's my complete code now... What's the problem???? Obviously I've missed
something..
Option Compare Database
Public Sub TestRun()
' Define the array for the values to be put into
Dim MyArray() As String, i
' Pass the array into the subprocedure. The subprocedure will populate
this array and
' return it back to you. This happens b/c you are setting the parameter As
a ByRef value.
i = HarmonicMean(GetNumbers(MyArray))
End Sub
Public Function GetNumbers(ByRef arrNumbers() As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim arrTemp() As String
Dim intIndex As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ProjectsAging")
If (rst.BOF And rst.EOF) Then
' no records to process
Else
' has records to process
intIndex = 0
Do While Not rst.EOF
ReDim Preserve arrTemp(intIndex)
arrTemp(intIndex) = rst("DaysToPay")
intIndex = intIndex + 1
rst.MoveNext
Loop
End If
Set rst = Nothing
Set dbs = Nothing
End Function
Public Function HarmonicMean(ParamArray MyArray() As Variant) As Double
On Error GoTo ErrorHandler
Dim varNum As Variant
Dim dblTotal As Double
Dim dblCount As Double
dblCount = 0 'The number of elements in the array
dblTotal = 0 '
For Each varNum In MyArray()
If IsNumeric(varNum) And Not IsEmpty(varNum) Then 'Check if the variable
is a valid number
varNum = (1 / varNum)
dblTotal = dblTotal + CDbl(varNum) 'Expressely convert variant data
to double
dblCount = dblCount + 1
Else
Err.Raise vbObjectError + 1000, , "Only numeric values can be used."
Exit Function
End If
Next
HarmonicMean = (1 / (dblTotal / dblCount))
Exit_ErrorHandler:
Exit Function
ErrorHandler:
MsgBox Err.Number & " " & Err.Description, vbInformation
Resume Exit_ErrorHandler
End Function
--
THX cs
something..
Option Compare Database
Public Sub TestRun()
' Define the array for the values to be put into
Dim MyArray() As String, i
' Pass the array into the subprocedure. The subprocedure will populate
this array and
' return it back to you. This happens b/c you are setting the parameter As
a ByRef value.
i = HarmonicMean(GetNumbers(MyArray))
End Sub
Public Function GetNumbers(ByRef arrNumbers() As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim arrTemp() As String
Dim intIndex As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ProjectsAging")
If (rst.BOF And rst.EOF) Then
' no records to process
Else
' has records to process
intIndex = 0
Do While Not rst.EOF
ReDim Preserve arrTemp(intIndex)
arrTemp(intIndex) = rst("DaysToPay")
intIndex = intIndex + 1
rst.MoveNext
Loop
End If
Set rst = Nothing
Set dbs = Nothing
End Function
Public Function HarmonicMean(ParamArray MyArray() As Variant) As Double
On Error GoTo ErrorHandler
Dim varNum As Variant
Dim dblTotal As Double
Dim dblCount As Double
dblCount = 0 'The number of elements in the array
dblTotal = 0 '
For Each varNum In MyArray()
If IsNumeric(varNum) And Not IsEmpty(varNum) Then 'Check if the variable
is a valid number
varNum = (1 / varNum)
dblTotal = dblTotal + CDbl(varNum) 'Expressely convert variant data
to double
dblCount = dblCount + 1
Else
Err.Raise vbObjectError + 1000, , "Only numeric values can be used."
Exit Function
End If
Next
HarmonicMean = (1 / (dblTotal / dblCount))
Exit_ErrorHandler:
Exit Function
ErrorHandler:
MsgBox Err.Number & " " & Err.Description, vbInformation
Resume Exit_ErrorHandler
End Function
--
THX cs
RobFMS said:I've kept it in DAO so that you can manage it easier.
Its untested b/c I do not have the table/query you have but it should be
well enough for you to work with.
Let me know if you have any other questions.
Rob Mastrostefano
Public Sub TestRun()
' Define the array for the values to be put into
Dim MyArray() As String
' Pass the array into the subprocedure. The subprocedure will populate
this array
' and return it back to you. This happens b/c you are setting the
parameter as a
' ByRef value.
GetNumbers (MyArray)
End Sub
Public Sub GetNumbers( _
ByRef arrNumbers() As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim arrTemp() As String
Dim intIndex As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ProjectsAging")
If (rst.BOF And rst.EOF) Then
' no records to process
Else
' has records to process
intIndex = 0
Do While Not rst.EOF
ReDim Preserve arrTemp(intIndex)
arrTemp(intIndex) = rst("DaysToPay")
intIndex = intIndex + 1
rst.MoveNext
Loop
End If
Set rst = Nothing
Set dbs = Nothing
End Sub
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
Cydney said:I'm not sure how to use that function. Could I somehow convert my code to
use
the .GetRows function? Here's my code
Function GetNumbers()
Dim db As Database
Dim Rst As Recordset
Dim MyNum As Double, myString, MyArray
Set db = CurrentDb()
Set Rst = db.OpenRecordset("ProjectsAging")
If Rst.RecordCount = 0 Then
Exit Function
End If
Rst.MoveFirst
Do While Not Rst.EOF
MyNum = Rst![DaysToPay]
Rst.MoveNext
myString = myString & MyNum & ", "
Loop
MyArray = Split(myString, ",", -1)
Numbers = HarmonicMean(MyArray)
Set Rst = Nothing
Set db = Nothing
End Function
This provides a "string" in the correct format..but of course it does me
no
good if I can't run my HarmonicMean function on it...
--
THX cs
RobFMS said:How is this data stored? In ADO, there is a method called .GetRows (I
believe that's the correct name). This stuffs the data into an array.
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
FMS Advanced Systems Group
http://www.fmsasg.com/
Is there a function I can use like "Join(fieldname(), ", ") that will
join
numbers instead of strings into an array?
R
RobFMS
Not being able to run and test the code, it gets a little iffy at times...
You need to explicitly define the function's return value.
For Example:
Public Function GetNumbers( ... ) as {String, Boolean, Integer, etc...}
End Function
If the HarmonicMean is returning a value with decimals, try something like:
Public Function GetNumbers (ByRef arrNumbers() As String) as Decimal
End Function
Try this new TestRun() function
Public Sub TestRun()
' Define the array for the values to be put into
' --------------------------------------------------------------------
Dim MyArray() As String
' This initializes the array. With this set, we should be able to pass it
into the GetNumbers().
Redim MyArray(0)
' Pass the array into the subprocedure. The subprocedure will populate
this array and
' return it back to you. This happens b/c you are setting the parameter As
a ByRef value.
i = HarmonicMean(GetNumbers(MyArray))
End Sub
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
You need to explicitly define the function's return value.
For Example:
Public Function GetNumbers( ... ) as {String, Boolean, Integer, etc...}
End Function
If the HarmonicMean is returning a value with decimals, try something like:
Public Function GetNumbers (ByRef arrNumbers() As String) as Decimal
End Function
Try this new TestRun() function
Public Sub TestRun()
' Define the array for the values to be put into
' --------------------------------------------------------------------
Dim MyArray() As String
' This initializes the array. With this set, we should be able to pass it
into the GetNumbers().
Redim MyArray(0)
' Pass the array into the subprocedure. The subprocedure will populate
this array and
' return it back to you. This happens b/c you are setting the parameter As
a ByRef value.
i = HarmonicMean(GetNumbers(MyArray))
End Sub
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
Cydney said:Here's my complete code now... What's the problem???? Obviously I've
missed
something..
Option Compare Database
Public Sub TestRun()
' Define the array for the values to be put into
Dim MyArray() As String, i
' Pass the array into the subprocedure. The subprocedure will populate
this array and
' return it back to you. This happens b/c you are setting the parameter
As
a ByRef value.
i = HarmonicMean(GetNumbers(MyArray))
End Sub
Public Function GetNumbers(ByRef arrNumbers() As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim arrTemp() As String
Dim intIndex As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ProjectsAging")
If (rst.BOF And rst.EOF) Then
' no records to process
Else
' has records to process
intIndex = 0
Do While Not rst.EOF
ReDim Preserve arrTemp(intIndex)
arrTemp(intIndex) = rst("DaysToPay")
intIndex = intIndex + 1
rst.MoveNext
Loop
End If
Set rst = Nothing
Set dbs = Nothing
End Function
Public Function HarmonicMean(ParamArray MyArray() As Variant) As Double
On Error GoTo ErrorHandler
Dim varNum As Variant
Dim dblTotal As Double
Dim dblCount As Double
dblCount = 0 'The number of elements in the array
dblTotal = 0 '
For Each varNum In MyArray()
If IsNumeric(varNum) And Not IsEmpty(varNum) Then 'Check if the
variable
is a valid number
varNum = (1 / varNum)
dblTotal = dblTotal + CDbl(varNum) 'Expressely convert variant data
to double
dblCount = dblCount + 1
Else
Err.Raise vbObjectError + 1000, , "Only numeric values can be
used."
Exit Function
End If
Next
HarmonicMean = (1 / (dblTotal / dblCount))
Exit_ErrorHandler:
Exit Function
ErrorHandler:
MsgBox Err.Number & " " & Err.Description, vbInformation
Resume Exit_ErrorHandler
End Function
--
THX cs
RobFMS said:I've kept it in DAO so that you can manage it easier.
Its untested b/c I do not have the table/query you have but it should be
well enough for you to work with.
Let me know if you have any other questions.
Rob Mastrostefano
Public Sub TestRun()
' Define the array for the values to be put into
Dim MyArray() As String
' Pass the array into the subprocedure. The subprocedure will populate
this array
' and return it back to you. This happens b/c you are setting the
parameter as a
' ByRef value.
GetNumbers (MyArray)
End Sub
Public Sub GetNumbers( _
ByRef arrNumbers() As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim arrTemp() As String
Dim intIndex As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ProjectsAging")
If (rst.BOF And rst.EOF) Then
' no records to process
Else
' has records to process
intIndex = 0
Do While Not rst.EOF
ReDim Preserve arrTemp(intIndex)
arrTemp(intIndex) = rst("DaysToPay")
intIndex = intIndex + 1
rst.MoveNext
Loop
End If
Set rst = Nothing
Set dbs = Nothing
End Sub
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
Cydney said:I'm not sure how to use that function. Could I somehow convert my code
to
use
the .GetRows function? Here's my code
Function GetNumbers()
Dim db As Database
Dim Rst As Recordset
Dim MyNum As Double, myString, MyArray
Set db = CurrentDb()
Set Rst = db.OpenRecordset("ProjectsAging")
If Rst.RecordCount = 0 Then
Exit Function
End If
Rst.MoveFirst
Do While Not Rst.EOF
MyNum = Rst![DaysToPay]
Rst.MoveNext
myString = myString & MyNum & ", "
Loop
MyArray = Split(myString, ",", -1)
Numbers = HarmonicMean(MyArray)
Set Rst = Nothing
Set db = Nothing
End Function
This provides a "string" in the correct format..but of course it does
me
no
good if I can't run my HarmonicMean function on it...
--
THX cs
:
How is this data stored? In ADO, there is a method called .GetRows (I
believe that's the correct name). This stuffs the data into an array.
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
FMS Advanced Systems Group
http://www.fmsasg.com/
Is there a function I can use like "Join(fieldname(), ", ") that
will
join
numbers instead of strings into an array?
O
onedaywhen
Cydney said:Here's my complete code now... What's the problem???? Obviously I've missed
something..
The point of a DBMS <g>? You are stuck in a procedural code mindset.
You need to think in terms of sets. Use SQL rather than VBA. SQL is a
declarative language: you tell it what you want and the data engine
decides how best to get the results based on its dedicated knowledge of
its own implementation e.g.
SELECT SUM(1 / DaysToPay) FROM ProjectsAging
SQL has one data structure, being the table. You don't need arrays.
Your procedural code says, 'I can get the data better than the SQL
engine.' Unlikely, don't you think <g>?
Jamie.
--
G
Guest
Unless I'm misunderstanding your SQL, it doesn't give me the correct answer.
I did try that route first and it didn't work for me. I'm needing to create
an array for the harmonic mean calculation to work.
Your answer gives me: 0.301
The correct answer is: 36.576
My array of numbers are: DaysToPay = 42,39,49,52,22,21,28,36,90,59,37
I did try that route first and it didn't work for me. I'm needing to create
an array for the harmonic mean calculation to work.
Your answer gives me: 0.301
The correct answer is: 36.576
My array of numbers are: DaysToPay = 42,39,49,52,22,21,28,36,90,59,37
G
Guest
I modified my code to match yours....still an error.
Right now the HarmonicMean function variant doesn't seem to be getting
passed the numbers in the array. It gives me an error such as: "11 Division
by Zero"
The correct answer should be : 36.576
to an array of: DaysToPay = 42,39,49,52,22,21,28,36,90,59,37
--
THX cs
Right now the HarmonicMean function variant doesn't seem to be getting
passed the numbers in the array. It gives me an error such as: "11 Division
by Zero"
The correct answer should be : 36.576
to an array of: DaysToPay = 42,39,49,52,22,21,28,36,90,59,37
--
THX cs
RobFMS said:Not being able to run and test the code, it gets a little iffy at times...
You need to explicitly define the function's return value.
For Example:
Public Function GetNumbers( ... ) as {String, Boolean, Integer, etc...}
End Function
If the HarmonicMean is returning a value with decimals, try something like:
Public Function GetNumbers (ByRef arrNumbers() As String) as Decimal
End Function
Try this new TestRun() function
Public Sub TestRun()
' Define the array for the values to be put into
' --------------------------------------------------------------------
Dim MyArray() As String
' This initializes the array. With this set, we should be able to pass it
into the GetNumbers().
Redim MyArray(0)
' Pass the array into the subprocedure. The subprocedure will populate
this array and
' return it back to you. This happens b/c you are setting the parameter As
a ByRef value.
i = HarmonicMean(GetNumbers(MyArray))
End Sub
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
Cydney said:Here's my complete code now... What's the problem???? Obviously I've
missed
something..
Option Compare Database
Public Sub TestRun()
' Define the array for the values to be put into
Dim MyArray() As String, i
' Pass the array into the subprocedure. The subprocedure will populate
this array and
' return it back to you. This happens b/c you are setting the parameter
As
a ByRef value.
i = HarmonicMean(GetNumbers(MyArray))
End Sub
Public Function GetNumbers(ByRef arrNumbers() As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim arrTemp() As String
Dim intIndex As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ProjectsAging")
If (rst.BOF And rst.EOF) Then
' no records to process
Else
' has records to process
intIndex = 0
Do While Not rst.EOF
ReDim Preserve arrTemp(intIndex)
arrTemp(intIndex) = rst("DaysToPay")
intIndex = intIndex + 1
rst.MoveNext
Loop
End If
Set rst = Nothing
Set dbs = Nothing
End Function
Public Function HarmonicMean(ParamArray MyArray() As Variant) As Double
On Error GoTo ErrorHandler
Dim varNum As Variant
Dim dblTotal As Double
Dim dblCount As Double
dblCount = 0 'The number of elements in the array
dblTotal = 0 '
For Each varNum In MyArray()
If IsNumeric(varNum) And Not IsEmpty(varNum) Then 'Check if the
variable
is a valid number
varNum = (1 / varNum)
dblTotal = dblTotal + CDbl(varNum) 'Expressely convert variant data
to double
dblCount = dblCount + 1
Else
Err.Raise vbObjectError + 1000, , "Only numeric values can be
used."
Exit Function
End If
Next
HarmonicMean = (1 / (dblTotal / dblCount))
Exit_ErrorHandler:
Exit Function
ErrorHandler:
MsgBox Err.Number & " " & Err.Description, vbInformation
Resume Exit_ErrorHandler
End Function
--
THX cs
RobFMS said:I've kept it in DAO so that you can manage it easier.
Its untested b/c I do not have the table/query you have but it should be
well enough for you to work with.
Let me know if you have any other questions.
Rob Mastrostefano
Public Sub TestRun()
' Define the array for the values to be put into
Dim MyArray() As String
' Pass the array into the subprocedure. The subprocedure will populate
this array
' and return it back to you. This happens b/c you are setting the
parameter as a
' ByRef value.
GetNumbers (MyArray)
End Sub
Public Sub GetNumbers( _
ByRef arrNumbers() As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim arrTemp() As String
Dim intIndex As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ProjectsAging")
If (rst.BOF And rst.EOF) Then
' no records to process
Else
' has records to process
intIndex = 0
Do While Not rst.EOF
ReDim Preserve arrTemp(intIndex)
arrTemp(intIndex) = rst("DaysToPay")
intIndex = intIndex + 1
rst.MoveNext
Loop
End If
Set rst = Nothing
Set dbs = Nothing
End Sub
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
I'm not sure how to use that function. Could I somehow convert my code
to
use
the .GetRows function? Here's my code
Function GetNumbers()
Dim db As Database
Dim Rst As Recordset
Dim MyNum As Double, myString, MyArray
Set db = CurrentDb()
Set Rst = db.OpenRecordset("ProjectsAging")
If Rst.RecordCount = 0 Then
Exit Function
End If
Rst.MoveFirst
Do While Not Rst.EOF
MyNum = Rst![DaysToPay]
Rst.MoveNext
myString = myString & MyNum & ", "
Loop
MyArray = Split(myString, ",", -1)
Numbers = HarmonicMean(MyArray)
Set Rst = Nothing
Set db = Nothing
End Function
This provides a "string" in the correct format..but of course it does
me
no
good if I can't run my HarmonicMean function on it...
--
THX cs
:
How is this data stored? In ADO, there is a method called .GetRows (I
believe that's the correct name). This stuffs the data into an array.
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
FMS Advanced Systems Group
http://www.fmsasg.com/
Is there a function I can use like "Join(fieldname(), ", ") that
will
join
numbers instead of strings into an array?
R
RobFMS
Cydney
Can you zip the database and send it to me? Don't send it as an .MDB or the
email system will reject it.
Sent it to:
Rob@
fmsinc
..com
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
Can you zip the database and send it to me? Don't send it as an .MDB or the
email system will reject it.
Sent it to:
Rob@
fmsinc
..com
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
Cydney said:I modified my code to match yours....still an error.
Right now the HarmonicMean function variant doesn't seem to be getting
passed the numbers in the array. It gives me an error such as: "11
Division
by Zero"
The correct answer should be : 36.576
to an array of: DaysToPay = 42,39,49,52,22,21,28,36,90,59,37
--
THX cs
RobFMS said:Not being able to run and test the code, it gets a little iffy at
times...
You need to explicitly define the function's return value.
For Example:
Public Function GetNumbers( ... ) as {String, Boolean, Integer, etc...}
End Function
If the HarmonicMean is returning a value with decimals, try something
like:
Public Function GetNumbers (ByRef arrNumbers() As String) as Decimal
End Function
Try this new TestRun() function
Public Sub TestRun()
' Define the array for the values to be put into
' --------------------------------------------------------------------
Dim MyArray() As String
' This initializes the array. With this set, we should be able to pass
it
into the GetNumbers().
Redim MyArray(0)
' Pass the array into the subprocedure. The subprocedure will populate
this array and
' return it back to you. This happens b/c you are setting the parameter
As
a ByRef value.
i = HarmonicMean(GetNumbers(MyArray))
End Sub
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
Cydney said:Here's my complete code now... What's the problem???? Obviously I've
missed
something..
Option Compare Database
Public Sub TestRun()
' Define the array for the values to be put into
Dim MyArray() As String, i
' Pass the array into the subprocedure. The subprocedure will populate
this array and
' return it back to you. This happens b/c you are setting the
parameter
As
a ByRef value.
i = HarmonicMean(GetNumbers(MyArray))
End Sub
Public Function GetNumbers(ByRef arrNumbers() As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim arrTemp() As String
Dim intIndex As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ProjectsAging")
If (rst.BOF And rst.EOF) Then
' no records to process
Else
' has records to process
intIndex = 0
Do While Not rst.EOF
ReDim Preserve arrTemp(intIndex)
arrTemp(intIndex) = rst("DaysToPay")
intIndex = intIndex + 1
rst.MoveNext
Loop
End If
Set rst = Nothing
Set dbs = Nothing
End Function
Public Function HarmonicMean(ParamArray MyArray() As Variant) As Double
On Error GoTo ErrorHandler
Dim varNum As Variant
Dim dblTotal As Double
Dim dblCount As Double
dblCount = 0 'The number of elements in the array
dblTotal = 0 '
For Each varNum In MyArray()
If IsNumeric(varNum) And Not IsEmpty(varNum) Then 'Check if the
variable
is a valid number
varNum = (1 / varNum)
dblTotal = dblTotal + CDbl(varNum) 'Expressely convert variant
data
to double
dblCount = dblCount + 1
Else
Err.Raise vbObjectError + 1000, , "Only numeric values can be
used."
Exit Function
End If
Next
HarmonicMean = (1 / (dblTotal / dblCount))
Exit_ErrorHandler:
Exit Function
ErrorHandler:
MsgBox Err.Number & " " & Err.Description, vbInformation
Resume Exit_ErrorHandler
End Function
--
THX cs
:
I've kept it in DAO so that you can manage it easier.
Its untested b/c I do not have the table/query you have but it should
be
well enough for you to work with.
Let me know if you have any other questions.
Rob Mastrostefano
Public Sub TestRun()
' Define the array for the values to be put into
Dim MyArray() As String
' Pass the array into the subprocedure. The subprocedure will
populate
this array
' and return it back to you. This happens b/c you are setting the
parameter as a
' ByRef value.
GetNumbers (MyArray)
End Sub
Public Sub GetNumbers( _
ByRef arrNumbers() As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim arrTemp() As String
Dim intIndex As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ProjectsAging")
If (rst.BOF And rst.EOF) Then
' no records to process
Else
' has records to process
intIndex = 0
Do While Not rst.EOF
ReDim Preserve arrTemp(intIndex)
arrTemp(intIndex) = rst("DaysToPay")
intIndex = intIndex + 1
rst.MoveNext
Loop
End If
Set rst = Nothing
Set dbs = Nothing
End Sub
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
I'm not sure how to use that function. Could I somehow convert my
code
to
use
the .GetRows function? Here's my code
Function GetNumbers()
Dim db As Database
Dim Rst As Recordset
Dim MyNum As Double, myString, MyArray
Set db = CurrentDb()
Set Rst = db.OpenRecordset("ProjectsAging")
If Rst.RecordCount = 0 Then
Exit Function
End If
Rst.MoveFirst
Do While Not Rst.EOF
MyNum = Rst![DaysToPay]
Rst.MoveNext
myString = myString & MyNum & ", "
Loop
MyArray = Split(myString, ",", -1)
Numbers = HarmonicMean(MyArray)
Set Rst = Nothing
Set db = Nothing
End Function
This provides a "string" in the correct format..but of course it
does
me
no
good if I can't run my HarmonicMean function on it...
--
THX cs
:
How is this data stored? In ADO, there is a method called .GetRows
(I
believe that's the correct name). This stuffs the data into an
array.
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
FMS Advanced Systems Group
http://www.fmsasg.com/
Is there a function I can use like "Join(fieldname(), ", ") that
will
join
numbers instead of strings into an array?
G
Guest
Thanks!! your code was excellent. I had to do a little tweaking to
accommodate some anomalies in my own dataset, but the code itself was
appropriate.
Here is that code for others to use.
Thanks again Rob! Good turn-around!
Option Compare Database
Public Sub TestRun()
' Define the array for the values to be put into
Dim MyArray() As Integer
ReDim MyArray(0)
' Pass the array into the subprocedure. The subprocedure will populate
this array and
' return it back to you. This happens b/c you are setting the parameter As
a ByRef value.
Call GetNumbers(MyArray)
Dim i As Double
i = HarmonicMean(MyArray)
MsgBox "i = " & i
End Sub
Public Function GetNumbers(ByRef arrNumbers() As Integer) As Double
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intIndex As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ProjectsAging")
If (rst.BOF And rst.EOF) Then
' no records to process
Else
' has records to process
intIndex = 0
Do While Not rst.EOF
ReDim Preserve arrNumbers(intIndex)
arrNumbers(intIndex) = rst("DaysToPay")
intIndex = intIndex + 1
rst.MoveNext
Loop
End If
' Set rst = Nothing
' Set dbs = Nothing
End Function
Public Function HarmonicMean(ByRef MyArray() As Integer) As Double
On Error GoTo ErrorHandler
Dim varNum As Variant
Dim dblTotal As Double
Dim dblCount As Double
dblCount = 0 'The number of elements in the array
dblTotal = 0 '
For Each varNum In MyArray()
If IsNumeric(varNum) And Not IsEmpty(varNum) Then 'Check if the variable
is a valid number
varNum = (1 / varNum)
dblTotal = dblTotal + CDbl(varNum) 'Expressely convert variant data
to double
dblCount = dblCount + 1
Else
Err.Raise vbObjectError + 1000, , "Only numeric values can be used."
Exit Function
End If
Next
HarmonicMean = (1 / (dblTotal / dblCount))
Exit_ErrorHandler:
Exit Function
ErrorHandler:
MsgBox Err.Number & " " & Err.Description, vbInformation
Resume Exit_ErrorHandler
End Function
--
THX cs
accommodate some anomalies in my own dataset, but the code itself was
appropriate.
Here is that code for others to use.
Thanks again Rob! Good turn-around!
Option Compare Database
Public Sub TestRun()
' Define the array for the values to be put into
Dim MyArray() As Integer
ReDim MyArray(0)
' Pass the array into the subprocedure. The subprocedure will populate
this array and
' return it back to you. This happens b/c you are setting the parameter As
a ByRef value.
Call GetNumbers(MyArray)
Dim i As Double
i = HarmonicMean(MyArray)
MsgBox "i = " & i
End Sub
Public Function GetNumbers(ByRef arrNumbers() As Integer) As Double
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intIndex As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ProjectsAging")
If (rst.BOF And rst.EOF) Then
' no records to process
Else
' has records to process
intIndex = 0
Do While Not rst.EOF
ReDim Preserve arrNumbers(intIndex)
arrNumbers(intIndex) = rst("DaysToPay")
intIndex = intIndex + 1
rst.MoveNext
Loop
End If
' Set rst = Nothing
' Set dbs = Nothing
End Function
Public Function HarmonicMean(ByRef MyArray() As Integer) As Double
On Error GoTo ErrorHandler
Dim varNum As Variant
Dim dblTotal As Double
Dim dblCount As Double
dblCount = 0 'The number of elements in the array
dblTotal = 0 '
For Each varNum In MyArray()
If IsNumeric(varNum) And Not IsEmpty(varNum) Then 'Check if the variable
is a valid number
varNum = (1 / varNum)
dblTotal = dblTotal + CDbl(varNum) 'Expressely convert variant data
to double
dblCount = dblCount + 1
Else
Err.Raise vbObjectError + 1000, , "Only numeric values can be used."
Exit Function
End If
Next
HarmonicMean = (1 / (dblTotal / dblCount))
Exit_ErrorHandler:
Exit Function
ErrorHandler:
MsgBox Err.Number & " " & Err.Description, vbInformation
Resume Exit_ErrorHandler
End Function
--
THX cs
RobFMS said:Cydney
Can you zip the database and send it to me? Don't send it as an .MDB or the
email system will reject it.
Sent it to:
Rob@
fmsinc
..com
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
Cydney said:I modified my code to match yours....still an error.
Right now the HarmonicMean function variant doesn't seem to be getting
passed the numbers in the array. It gives me an error such as: "11
Division
by Zero"
The correct answer should be : 36.576
to an array of: DaysToPay = 42,39,49,52,22,21,28,36,90,59,37
--
THX cs
RobFMS said:Not being able to run and test the code, it gets a little iffy at
times...
You need to explicitly define the function's return value.
For Example:
Public Function GetNumbers( ... ) as {String, Boolean, Integer, etc...}
End Function
If the HarmonicMean is returning a value with decimals, try something
like:
Public Function GetNumbers (ByRef arrNumbers() As String) as Decimal
End Function
Try this new TestRun() function
Public Sub TestRun()
' Define the array for the values to be put into
' --------------------------------------------------------------------
Dim MyArray() As String
' This initializes the array. With this set, we should be able to pass
it
into the GetNumbers().
Redim MyArray(0)
' Pass the array into the subprocedure. The subprocedure will populate
this array and
' return it back to you. This happens b/c you are setting the parameter
As
a ByRef value.
i = HarmonicMean(GetNumbers(MyArray))
End Sub
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
Here's my complete code now... What's the problem???? Obviously I've
missed
something..
Option Compare Database
Public Sub TestRun()
' Define the array for the values to be put into
Dim MyArray() As String, i
' Pass the array into the subprocedure. The subprocedure will populate
this array and
' return it back to you. This happens b/c you are setting the
parameter
As
a ByRef value.
i = HarmonicMean(GetNumbers(MyArray))
End Sub
Public Function GetNumbers(ByRef arrNumbers() As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim arrTemp() As String
Dim intIndex As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ProjectsAging")
If (rst.BOF And rst.EOF) Then
' no records to process
Else
' has records to process
intIndex = 0
Do While Not rst.EOF
ReDim Preserve arrTemp(intIndex)
arrTemp(intIndex) = rst("DaysToPay")
intIndex = intIndex + 1
rst.MoveNext
Loop
End If
Set rst = Nothing
Set dbs = Nothing
End Function
Public Function HarmonicMean(ParamArray MyArray() As Variant) As Double
On Error GoTo ErrorHandler
Dim varNum As Variant
Dim dblTotal As Double
Dim dblCount As Double
dblCount = 0 'The number of elements in the array
dblTotal = 0 '
For Each varNum In MyArray()
If IsNumeric(varNum) And Not IsEmpty(varNum) Then 'Check if the
variable
is a valid number
varNum = (1 / varNum)
dblTotal = dblTotal + CDbl(varNum) 'Expressely convert variant
data
to double
dblCount = dblCount + 1
Else
Err.Raise vbObjectError + 1000, , "Only numeric values can be
used."
Exit Function
End If
Next
HarmonicMean = (1 / (dblTotal / dblCount))
Exit_ErrorHandler:
Exit Function
ErrorHandler:
MsgBox Err.Number & " " & Err.Description, vbInformation
Resume Exit_ErrorHandler
End Function
--
THX cs
:
I've kept it in DAO so that you can manage it easier.
Its untested b/c I do not have the table/query you have but it should
be
well enough for you to work with.
Let me know if you have any other questions.
Rob Mastrostefano
Public Sub TestRun()
' Define the array for the values to be put into
Dim MyArray() As String
' Pass the array into the subprocedure. The subprocedure will
populate
this array
' and return it back to you. This happens b/c you are setting the
parameter as a
' ByRef value.
GetNumbers (MyArray)
End Sub
Public Sub GetNumbers( _
ByRef arrNumbers() As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim arrTemp() As String
Dim intIndex As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ProjectsAging")
If (rst.BOF And rst.EOF) Then
' no records to process
Else
' has records to process
intIndex = 0
Do While Not rst.EOF
ReDim Preserve arrTemp(intIndex)
arrTemp(intIndex) = rst("DaysToPay")
intIndex = intIndex + 1
rst.MoveNext
Loop
End If
Set rst = Nothing
Set dbs = Nothing
End Sub
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
I'm not sure how to use that function. Could I somehow convert my
code
to
use
the .GetRows function? Here's my code
Function GetNumbers()
Dim db As Database
Dim Rst As Recordset
Dim MyNum As Double, myString, MyArray
Set db = CurrentDb()
Set Rst = db.OpenRecordset("ProjectsAging")
If Rst.RecordCount = 0 Then
Exit Function
End If
Rst.MoveFirst
Do While Not Rst.EOF
MyNum = Rst![DaysToPay]
Rst.MoveNext
myString = myString & MyNum & ", "
Loop
MyArray = Split(myString, ",", -1)
Numbers = HarmonicMean(MyArray)
Set Rst = Nothing
Set db = Nothing
End Function
This provides a "string" in the correct format..but of course it
does
me
no
good if I can't run my HarmonicMean function on it...
--
THX cs
:
How is this data stored? In ADO, there is a method called .GetRows
(I
believe that's the correct name). This stuffs the data into an
array.
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
FMS Advanced Systems Group
http://www.fmsasg.com/
"Cydney" <[email protected]> wrote in message
R
RobFMS
Glad I could help!
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
Cydney said:Thanks!! your code was excellent. I had to do a little tweaking to
accommodate some anomalies in my own dataset, but the code itself was
appropriate.
Here is that code for others to use.
Thanks again Rob! Good turn-around!
Option Compare Database
Public Sub TestRun()
' Define the array for the values to be put into
Dim MyArray() As Integer
ReDim MyArray(0)
' Pass the array into the subprocedure. The subprocedure will populate
this array and
' return it back to you. This happens b/c you are setting the parameter
As
a ByRef value.
Call GetNumbers(MyArray)
Dim i As Double
i = HarmonicMean(MyArray)
MsgBox "i = " & i
End Sub
Public Function GetNumbers(ByRef arrNumbers() As Integer) As Double
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intIndex As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ProjectsAging")
If (rst.BOF And rst.EOF) Then
' no records to process
Else
' has records to process
intIndex = 0
Do While Not rst.EOF
ReDim Preserve arrNumbers(intIndex)
arrNumbers(intIndex) = rst("DaysToPay")
intIndex = intIndex + 1
rst.MoveNext
Loop
End If
' Set rst = Nothing
' Set dbs = Nothing
End Function
Public Function HarmonicMean(ByRef MyArray() As Integer) As Double
On Error GoTo ErrorHandler
Dim varNum As Variant
Dim dblTotal As Double
Dim dblCount As Double
dblCount = 0 'The number of elements in the array
dblTotal = 0 '
For Each varNum In MyArray()
If IsNumeric(varNum) And Not IsEmpty(varNum) Then 'Check if the
variable
is a valid number
varNum = (1 / varNum)
dblTotal = dblTotal + CDbl(varNum) 'Expressely convert variant data
to double
dblCount = dblCount + 1
Else
Err.Raise vbObjectError + 1000, , "Only numeric values can be
used."
Exit Function
End If
Next
HarmonicMean = (1 / (dblTotal / dblCount))
Exit_ErrorHandler:
Exit Function
ErrorHandler:
MsgBox Err.Number & " " & Err.Description, vbInformation
Resume Exit_ErrorHandler
End Function
--
THX cs
RobFMS said:Cydney
Can you zip the database and send it to me? Don't send it as an .MDB or
the
email system will reject it.
Sent it to:
Rob@
fmsinc
..com
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
Cydney said:I modified my code to match yours....still an error.
Right now the HarmonicMean function variant doesn't seem to be getting
passed the numbers in the array. It gives me an error such as: "11
Division
by Zero"
The correct answer should be : 36.576
to an array of: DaysToPay = 42,39,49,52,22,21,28,36,90,59,37
--
THX cs
:
Not being able to run and test the code, it gets a little iffy at
times...
You need to explicitly define the function's return value.
For Example:
Public Function GetNumbers( ... ) as {String, Boolean, Integer,
etc...}
End Function
If the HarmonicMean is returning a value with decimals, try something
like:
Public Function GetNumbers (ByRef arrNumbers() As String) as Decimal
End Function
Try this new TestRun() function
Public Sub TestRun()
' Define the array for the values to be put into
' --------------------------------------------------------------------
Dim MyArray() As String
' This initializes the array. With this set, we should be able to
pass
it
into the GetNumbers().
Redim MyArray(0)
' Pass the array into the subprocedure. The subprocedure will
populate
this array and
' return it back to you. This happens b/c you are setting the
parameter
As
a ByRef value.
i = HarmonicMean(GetNumbers(MyArray))
End Sub
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
Here's my complete code now... What's the problem???? Obviously
I've
missed
something..
Option Compare Database
Public Sub TestRun()
' Define the array for the values to be put into
Dim MyArray() As String, i
' Pass the array into the subprocedure. The subprocedure will
populate
this array and
' return it back to you. This happens b/c you are setting the
parameter
As
a ByRef value.
i = HarmonicMean(GetNumbers(MyArray))
End Sub
Public Function GetNumbers(ByRef arrNumbers() As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim arrTemp() As String
Dim intIndex As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ProjectsAging")
If (rst.BOF And rst.EOF) Then
' no records to process
Else
' has records to process
intIndex = 0
Do While Not rst.EOF
ReDim Preserve arrTemp(intIndex)
arrTemp(intIndex) = rst("DaysToPay")
intIndex = intIndex + 1
rst.MoveNext
Loop
End If
Set rst = Nothing
Set dbs = Nothing
End Function
Public Function HarmonicMean(ParamArray MyArray() As Variant) As
Double
On Error GoTo ErrorHandler
Dim varNum As Variant
Dim dblTotal As Double
Dim dblCount As Double
dblCount = 0 'The number of elements in the array
dblTotal = 0 '
For Each varNum In MyArray()
If IsNumeric(varNum) And Not IsEmpty(varNum) Then 'Check if the
variable
is a valid number
varNum = (1 / varNum)
dblTotal = dblTotal + CDbl(varNum) 'Expressely convert
variant
data
to double
dblCount = dblCount + 1
Else
Err.Raise vbObjectError + 1000, , "Only numeric values can be
used."
Exit Function
End If
Next
HarmonicMean = (1 / (dblTotal / dblCount))
Exit_ErrorHandler:
Exit Function
ErrorHandler:
MsgBox Err.Number & " " & Err.Description, vbInformation
Resume Exit_ErrorHandler
End Function
--
THX cs
:
I've kept it in DAO so that you can manage it easier.
Its untested b/c I do not have the table/query you have but it
should
be
well enough for you to work with.
Let me know if you have any other questions.
Rob Mastrostefano
Public Sub TestRun()
' Define the array for the values to be put into
Dim MyArray() As String
' Pass the array into the subprocedure. The subprocedure will
populate
this array
' and return it back to you. This happens b/c you are setting the
parameter as a
' ByRef value.
GetNumbers (MyArray)
End Sub
Public Sub GetNumbers( _
ByRef arrNumbers() As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim arrTemp() As String
Dim intIndex As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ProjectsAging")
If (rst.BOF And rst.EOF) Then
' no records to process
Else
' has records to process
intIndex = 0
Do While Not rst.EOF
ReDim Preserve arrTemp(intIndex)
arrTemp(intIndex) = rst("DaysToPay")
intIndex = intIndex + 1
rst.MoveNext
Loop
End If
Set rst = Nothing
Set dbs = Nothing
End Sub
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
I'm not sure how to use that function. Could I somehow convert my
code
to
use
the .GetRows function? Here's my code
Function GetNumbers()
Dim db As Database
Dim Rst As Recordset
Dim MyNum As Double, myString, MyArray
Set db = CurrentDb()
Set Rst = db.OpenRecordset("ProjectsAging")
If Rst.RecordCount = 0 Then
Exit Function
End If
Rst.MoveFirst
Do While Not Rst.EOF
MyNum = Rst![DaysToPay]
Rst.MoveNext
myString = myString & MyNum & ", "
Loop
MyArray = Split(myString, ",", -1)
Numbers = HarmonicMean(MyArray)
Set Rst = Nothing
Set db = Nothing
End Function
This provides a "string" in the correct format..but of course it
does
me
no
good if I can't run my HarmonicMean function on it...
--
THX cs
:
How is this data stored? In ADO, there is a method called
.GetRows
(I
believe that's the correct name). This stuffs the data into an
array.
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
FMS Advanced Systems Group
http://www.fmsasg.com/
"Cydney" <[email protected]> wrote in message