Undefined function in expression

[angelasg]

Hello.

I just built a custom function in Access 2002 called RoundQtrHour
which rounds a number of minutes to the nearest quarter hour as a
decimal.

I tried to use it in a form and query and I got the error message:
Undefined function 'RoundQtrHour' in expression.

I went the help menu and found:

I get the message "Undefined function in an expression."
You've bound a control on a data access page to a calculated field
that uses a function that isn't supported on data access pages.

Functions supported on a data access page

A control in a data access page or a form that is open in PivotTable
view or PivotChart view can contain an expression that uses a
Microsoft Visual Basic for Applications (VBA) function. However, the
expression will generate an error unless it appears on the following
list.

Note This limitation doesn't apply to expressions in tables,
queries, views, stored procedures, or user-defined functions that you
have opened in PivotTable or PivotChart view.

Evidently, I'm trying to do what can't be done. I don't understand
the purpose of being able to build a function but not use it.

Is there any way around this beside building a long formula directly
in the query?

Thanks in advance for any help.

Angela

 

[Mark]

Did you write the function in a standard module or a form's module? You
should be able to reference the function if it's in a standard module.

 

[angelasg]

I don't have much experience writing custom functions so I'm not quite
sure of the difference, but I believe it is a standard module.

 

['69 Camaro]

Hi, Angela.

You've bound a control on a data access page to a calculated field
that uses a function that isn't supported on data access pages.

Functions supported on a data access page

A control in a data access page or a form that is open in PivotTable
view or PivotChart view can contain an expression that uses a
Microsoft Visual Basic for Applications (VBA) function. However, the
expression will generate an error unless it appears on the following
list.

Are you even using a Data Access Page, Pivot Table or Pivot Chart? Probably
not.

There are several reasons to receive this error, not necessarily the reasons
listed in Access Help. Did you define the custom function in a standard
module, not a class module or a form's class module? Did you ensure that
the function is public, not private? Did you ensure that the name of the
module doesn't match the name of any procedure in that module? Did you
ensure that the function was loaded into memory before the query called the
function?

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Troy]

Put the word Public before the function declaration.

Function RoundQtrHour() as Decimal

changes to

Public Function RoundQtrHour() as Decimal

--
Troy

Troy Munford
Development Operations Manager
FMS, Inc.
www.fmsinc.com


I don't have much experience writing custom functions so I'm not quite
sure of the difference, but I believe it is a standard module.

 

[angelasg]

The function was already public and it still does not work. Here it
is. It does compile but I haven't had a chance to check the math
since I can't get it to work in the query.

Public Function RoundQtrHour(Num As Single) As Single
Hours = Num \ 60
minutes = Num Mod 60
If minutes < 7 Then QtrHour = 0 Else
If minutes >= 7 And minutes <= 22 Then QtrHour = 0.25 Else
If minutes >= 23 And minutes <= 37 Then QtrHour = 0.5 Else
If minutes >= 38 And minutes <= 52 Then QtrHour = 0.75 Else
If minutes >= 53 Then QtrHour = 1
RoundQtrHour = Hours + QtrHour
End Function

 

[Troy]

And the code or method you are using to call this function?

--
Troy

Troy Munford
Development Operations Manager
FMS, Inc.
www.fmsinc.com


The function was already public and it still does not work. Here it
is. It does compile but I haven't had a chance to check the math
since I can't get it to work in the query.

Public Function RoundQtrHour(Num As Single) As Single
Hours = Num \ 60
minutes = Num Mod 60
If minutes < 7 Then QtrHour = 0 Else
If minutes >= 7 And minutes <= 22 Then QtrHour = 0.25 Else
If minutes >= 23 And minutes <= 37 Then QtrHour = 0.5 Else
If minutes >= 38 And minutes <= 52 Then QtrHour = 0.75 Else
If minutes >= 53 Then QtrHour = 1
RoundQtrHour = Hours + QtrHour
End Function

 

[angelasg]

I'm building a query in design few. I am creating a new expression
with the user defined function by selecting it from the Expression
Builder dialog box in the functions section. I tried just simply
putting a constant as the argument of my function and I got the error
message.

 

[Mark]

I just tried you function and it does work. Don't take offense at this
seemingly obvious statement; I just want to be clear since you said that you
believe you put it in a standard module: A standard module is one that you
create from adding a module from the Modules tab in the Database window.

Ok, so I created a simple table and added a couple of records with numbers
in a field called Field1. In the query builder, I put "Rounded:
RoundQtrHour([Field1])" without the quotes in the "Field" row of one of the
columns, and got the correct results when I ran the query.

The "Rounded:" portion (with the colon character) designates the word
Rounded is a label for the column, and the rest is the call to your custom
function which gets the value from Field1 passed to it for calculation.

 

[Troy]

Before you do anything else, in the code window, click on Debug -->Compile.

If that fails, what specific piece of text is highlighted after you click OK
or move the message box out of the way?

If it compiles, try running it from code (e.g. Immediate window - Ctrl+G) to
see if it works correctly prior to calling it from your query.

?RoundQtrHour(12.27)

If that works then how the query is calling it is the problem, if not, then
the function is the problem.

--
Troy

Troy Munford
Development Operations Manager
FMS, Inc.
www.fmsinc.com



I'm building a query in design few. I am creating a new expression
with the user defined function by selecting it from the Expression
Builder dialog box in the functions section. I tried just simply
putting a constant as the argument of my function and I got the error
message.

 

[Troy]

One last possibility: You are missing a Reference?

http://support.microsoft.com/kb/275110

--
Troy

Troy Munford
Development Operations Manager
FMS, Inc.
www.fmsinc.com



I'm building a query in design few. I am creating a new expression
with the user defined function by selecting it from the Expression
Builder dialog box in the functions section. I tried just simply
putting a constant as the argument of my function and I got the error
message.

 

[angelasg]

Mark,

I recreated the function again the way you describe and it does work
now.

I did three things differently:

1. I'm at work now and using Access 2000 vs the Access 2002 I was
using at home.

2. When I created the function the first time, I went into VB from the
Tools option on the menu instead of from the Modules tab in the
database window. I did save it in the same place though and the first
one did show up in the Modules section.

3. And last, the first time I saved the function as the same name as
the function, i.e., RoundQtrHour. This time I left it as Module1.

I don't know which one of those things made the difference, but I will
try it again at home this evening.

Thank you so much for the help.

I just tried you function and it does work. Don't take offense at this
seemingly obvious statement; I just want to be clear since you said that you
believe you put it in a standard module: A standard module is one that you
create from adding a module from the Modules tab in the Database window.

Ok, so I created a simple table and added a couple of records with numbers
in a field called Field1. In the query builder, I put "Rounded:
RoundQtrHour([Field1])" without the quotes in the "Field" row of one of the
columns, and got the correct results when I ran the query.

The "Rounded:" portion (with the colon character) designates the word

Rounded is a label for the column, and the rest is the call to your custom
function which gets the value from Field1 passed to it for

calculation.

 

[Mark]

Angela,

Of the three items you mentioned, I think the one that was causing your
problem was #3. Access gets confused (as do most of us) when two objects
have the same name; such as a saved Module and a function or subroutine
inside that module. So, as a force of habit, I always name my modules
starting with "mod" like "modGlobalFunctions" or "modTableManipulation",
etc.

In any event, I'm glad you finally got it to work!