User Defined Functions in Calculated Fields

Also posted he
http://www.access-programmers.co.uk/...d.php?t=123289

I have been looking for information on how to use User Defined Functions in
Calculated Fields on a Report (Using MS Access 2003 Pro).

I need to use more than the six or eight Stat functions Access comes with.
So I created some user defined functions that incorporate
Excel.WorksheetFunction.Function. Which should work fine. The problem is the
UDF's won't even fire from the report - they just return #ERROR without
firing.

I have also used the wizard to 'point' to those functions in the
Functions|User Defined Functions list still producing the error.

Interestingly, searches on Google, this site and others return almost no
results. The 16 hits from this site typically pose the question with no
responses. The two Access books I have do not even mention User Defined
Functions.

Any source would be appreciated

TIA

Further :
Added a standard module in Access then
EG.


Code:
Public Function Median(ByVal aField As DAO.Field) As Double

Median = Excel.WorksheetFunction.Median(aField)

End FunctionAdded a standard TextBox control on a report footer with
ControlSource =FORMAT(Median([Ratio]), "Standard")

[Ratio] being a field on the Report Detail

NOTE:
Whether the syntax of the Function is correct or not is not the issue at
this point. What is the issue is the Function does not get called; placing a
break point revealed this...
__________________

Have you also set a reference to the Excel object model? If Access doesn't
know where to look for that Excel function, it might throw an error like
that...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

"doco" wrote in message
...
Also posted he
http://www.access-programmers.co.uk/...d.php?t=123289

I have been looking for information on how to use User Defined Functions
in
Calculated Fields on a Report (Using MS Access 2003 Pro).

I need to use more than the six or eight Stat functions Access comes with.
So I created some user defined functions that incorporate
Excel.WorksheetFunction.Function. Which should work fine. The problem is
the
UDF's won't even fire from the report - they just return #ERROR without
firing.

I have also used the wizard to 'point' to those functions in the
Functions|User Defined Functions list still producing the error.

Interestingly, searches on Google, this site and others return almost no
results. The 16 hits from this site typically pose the question with no
responses. The two Access books I have do not even mention User Defined
Functions.

Any source would be appreciated

TIA

Further :
Added a standard module in Access then
EG.


Code:
Public Function Median(ByVal aField As DAO.Field) As Double

Median = Excel.WorksheetFunction.Median(aField)

End FunctionAdded a standard TextBox control on a report footer with
ControlSource =FORMAT(Median([Ratio]), "Standard")

[Ratio] being a field on the Report Detail

NOTE:
Whether the syntax of the Function is correct or not is not the issue at
this point. What is the issue is the Function does not get called; placing
a
break point revealed this...
__________________

Yes I have, of course.

doco

"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
Have you also set a reference to the Excel object model? If Access
doesn't
know where to look for that Excel function, it might throw an error like
that...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

"doco" wrote in message
...
Also posted he
http://www.access-programmers.co.uk/...d.php?t=123289

I have been looking for information on how to use User Defined Functions
in
Calculated Fields on a Report (Using MS Access 2003 Pro).

I need to use more than the six or eight Stat functions Access comes
with.
So I created some user defined functions that incorporate
Excel.WorksheetFunction.Function. Which should work fine. The problem is
the
UDF's won't even fire from the report - they just return #ERROR without
firing.

I have also used the wizard to 'point' to those functions in the
Functions|User Defined Functions list still producing the error.

Interestingly, searches on Google, this site and others return almost no
results. The 16 hits from this site typically pose the question with no
responses. The two Access books I have do not even mention User Defined
Functions.

Any source would be appreciated

TIA

Further :
Added a standard module in Access then
EG.


Code:
Public Function Median(ByVal aField As DAO.Field) As Double

Median = Excel.WorksheetFunction.Median(aField)

End FunctionAdded a standard TextBox control on a report footer with
ControlSource =FORMAT(Median([Ratio]), "Standard")

[Ratio] being a field on the Report Detail

NOTE:
Whether the syntax of the Function is correct or not is not the issue at
this point. What is the issue is the Function does not get called;
placing
a
break point revealed this...
__________________

Taking the median of a single value doesn't make any sense, and
realistically that's all your function is capable of doing.

Take a look at my October, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access" for a discussion of how to compute medians in
Access. You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"doco" wrote in message
...
Also posted he
http://www.access-programmers.co.uk/...d.php?t=123289

I have been looking for information on how to use User Defined Functions
in Calculated Fields on a Report (Using MS Access 2003 Pro).

I need to use more than the six or eight Stat functions Access comes with.
So I created some user defined functions that incorporate
Excel.WorksheetFunction.Function. Which should work fine. The problem is
the UDF's won't even fire from the report - they just return #ERROR
without firing.

I have also used the wizard to 'point' to those functions in the
Functions|User Defined Functions list still producing the error.

Interestingly, searches on Google, this site and others return almost no
results. The 16 hits from this site typically pose the question with no
responses. The two Access books I have do not even mention User Defined
Functions.

Any source would be appreciated

TIA

Further :
Added a standard module in Access then
EG.


Code:
Public Function Median(ByVal aField As DAO.Field) As Double

Median = Excel.WorksheetFunction.Median(aField)

End FunctionAdded a standard TextBox control on a report footer with
ControlSource =FORMAT(Median([Ratio]), "Standard")

[Ratio] being a field on the Report Detail

NOTE:
Whether the syntax of the Function is correct or not is not the issue at
this point. What is the issue is the Function does not get called; placing
a break point revealed this...
__________________

As I stated in my question, I realize the code was inappropriate to begin, I
was merely wanting to get a call to debug while I created which it would not
do. However, I have since hand built Median, GeometricMean, WeightedMean,
AverageAbsoluteDeviation, etc. It sucks! Seems like there should be an
Analysis Toolpak addin or something...

Trying to work out a Frequency Distribution algorithm now - sucks even
worse. :-o

Thanks..

doco
"Douglas J. Steele" wrote in message
...
Taking the median of a single value doesn't make any sense, and
realistically that's all your function is capable of doing.

Take a look at my October, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access" for a discussion of how to compute medians in
Access. You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"doco" wrote in message
...
Also posted he
http://www.access-programmers.co.uk/...d.php?t=123289

I have been looking for information on how to use User Defined Functions
in Calculated Fields on a Report (Using MS Access 2003 Pro).

I need to use more than the six or eight Stat functions Access comes
with. So I created some user defined functions that incorporate
Excel.WorksheetFunction.Function. Which should work fine. The problem is
the UDF's won't even fire from the report - they just return #ERROR
without firing.

I have also used the wizard to 'point' to those functions in the
Functions|User Defined Functions list still producing the error.

Interestingly, searches on Google, this site and others return almost no
results. The 16 hits from this site typically pose the question with no
responses. The two Access books I have do not even mention User Defined
Functions.

Any source would be appreciated

TIA

Further :
Added a standard module in Access then
EG.


Code:
Public Function Median(ByVal aField As DAO.Field) As Double

Median = Excel.WorksheetFunction.Median(aField)

End FunctionAdded a standard TextBox control on a report footer with
ControlSource =FORMAT(Median([Ratio]), "Standard")

[Ratio] being a field on the Report Detail

NOTE:
Whether the syntax of the Function is correct or not is not the issue at
this point. What is the issue is the Function does not get called;
placing a break point revealed this...
__________________

If you're doing statistical work in Access, you might want to check out at
Total Access Statistics, from FMS:
http://www.fmsinc.com/products/statistics/index.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"doco" wrote in message
...
As I stated in my question, I realize the code was inappropriate to begin,
I was merely wanting to get a call to debug while I created which it would
not do. However, I have since hand built Median, GeometricMean,
WeightedMean, AverageAbsoluteDeviation, etc. It sucks! Seems like there
should be an Analysis Toolpak addin or something...

Trying to work out a Frequency Distribution algorithm now - sucks even
worse. :-o

Thanks..

doco
"Douglas J. Steele" wrote in message
...
Taking the median of a single value doesn't make any sense, and
realistically that's all your function is capable of doing.

Take a look at my October, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access" for a discussion of how to compute medians
in Access. You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"doco" wrote in message
...
Also posted he
http://www.access-programmers.co.uk/...d.php?t=123289

I have been looking for information on how to use User Defined Functions
in Calculated Fields on a Report (Using MS Access 2003 Pro).

I need to use more than the six or eight Stat functions Access comes
with. So I created some user defined functions that incorporate
Excel.WorksheetFunction.Function. Which should work fine. The problem is
the UDF's won't even fire from the report - they just return #ERROR
without firing.

I have also used the wizard to 'point' to those functions in the
Functions|User Defined Functions list still producing the error.

Interestingly, searches on Google, this site and others return almost no
results. The 16 hits from this site typically pose the question with no
responses. The two Access books I have do not even mention User Defined
Functions.

Any source would be appreciated

TIA

Further :
Added a standard module in Access then
EG.


Code:
Public Function Median(ByVal aField As DAO.Field) As Double

Median = Excel.WorksheetFunction.Median(aField)

End FunctionAdded a standard TextBox control on a report footer with
ControlSource =FORMAT(Median([Ratio]), "Standard")

[Ratio] being a field on the Report Detail

NOTE:
Whether the syntax of the Function is correct or not is not the issue at
this point. What is the issue is the Function does not get called;
placing a break point revealed this...
__________________

I found no mention of a reference in your original post. Since we're not
there, and you didn't mention it, it seemed an obvious first place to check.

Perhaps as obvious as your "of course"...g

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


"doco" wrote in message
...
Yes I have, of course.

doco

Ya, I overlooked it. The real bug-a-boo I found out, is with an addin I
have in Excel (which one?). For some reason when attempting to use
Excel.WorksheetFunction.SomeFunction from Access I get 'Out of Memory' error
in Access and a custom error popup from Excel (found after closing a few
windows). I had 29 data points in my Access list so it could not be a
memory error - I wouldn't think. The custom dialog is telling me to go to
Tools|Macro|Security|Trusted Publishers and "check 'Trust access to Visual
Basic Project'"; which is checked. Oui vey!

Thanks guys...

"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
I found no mention of a reference in your original post. Since we're not
there, and you didn't mention it, it seemed an obvious first place to
check.

Perhaps as obvious as your "of course"...g

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


"doco" wrote in message
...
Yes I have, of course.

doco

'fraid I don't have much experience in that arena.

Consider posting back here with your solution (after discovery!) ... others
might be able to benefit from your situation.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

"doco" wrote in message
...
Ya, I overlooked it. The real bug-a-boo I found out, is with an addin I
have in Excel (which one?). For some reason when attempting to use
Excel.WorksheetFunction.SomeFunction from Access I get 'Out of Memory'
error
in Access and a custom error popup from Excel (found after closing a few
windows). I had 29 data points in my Access list so it could not be a
memory error - I wouldn't think. The custom dialog is telling me to go to
Tools|Macro|Security|Trusted Publishers and "check 'Trust access to Visual
Basic Project'"; which is checked. Oui vey!

Thanks guys...

"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
I found no mention of a reference in your original post. Since we're not
there, and you didn't mention it, it seemed an obvious first place to
check.

Perhaps as obvious as your "of course"...g

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


"doco" wrote in message
...
Yes I have, of course.

doco

I certainly will. It's makin me nuts though. I have unloaded all known
addins but still am getting the error. I must finish my project now, but
will get back to this when I have time to tracker down!

Thanks
doco


"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
'fraid I don't have much experience in that arena.

Consider posting back here with your solution (after discovery!) ...
others
might be able to benefit from your situation.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

"doco" wrote in message
...
Ya, I overlooked it. The real bug-a-boo I found out, is with an addin I
have in Excel (which one?). For some reason when attempting to use
Excel.WorksheetFunction.SomeFunction from Access I get 'Out of Memory'
error
in Access and a custom error popup from Excel (found after closing a few
windows). I had 29 data points in my Access list so it could not be a
memory error - I wouldn't think. The custom dialog is telling me to go
to
Tools|Macro|Security|Trusted Publishers and "check 'Trust access to
Visual
Basic Project'"; which is checked. Oui vey!

Thanks guys...

"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in
message
...
I found no mention of a reference in your original post. Since we're
not
there, and you didn't mention it, it seemed an obvious first place to
check.

Perhaps as obvious as your "of course"...g

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


"doco" wrote in message
...
Yes I have, of course.

doco