Using user-defined functions within a query


Ive created a new module, by simply selecting new module in the database window and ive entered the following function code:

Function HrsMinsSecs(TimeVar As Double) As Double

Dim Hrs As Long
Dim Mins As String
Dim Secs As String

If IsNull(TimeVar) Then
    HrsMinsSecs = Null
    Mins = Format(DatePart("n", TimeVar), "00")
    Secs = Format(DatePart("s", TimeVar), "00")
    Hrs = (Fix(TimeVar) * 24) + DatePart("h", TimeVar)
    HrsMinsSecs = Hrs & ":" & Mins & ":" & Secs
End If

End Function

The function is supposed to show me the correct elapsed time, when performing date calculation. As usually if the difference is more that 24 hours only part is displayed.

I need to use the function in a query but when i try use the function in the field part of the query design grid, i get the error:

Undefined function <name> in expression. (Error 3085)

This happens even though my function appears within the expression builder.

Can anyone please help? Is my code incorred or do i need to define the function elsewhere.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

How do you use the function in the Query builder??

Also be sure that you dont have the module named the same as the rename the module if it is called the same as the function..

Rajesh_RajmangalAuthor Commented:

thanks for your help.

To access the function in i right click in an empty field, select expression builder, then functions, and select my defined function. I then replace the <expr> part with the name of another field in the query.

My function DID have the same name as the module, which i have now changed. When i try to run the query now i get the following error:

ambiguous name in expression

<Message> in query expression <expression>. (Error 3075)

can you please help?
Did you change the name of the function?
Then you also have to (of course) change your query..

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rajesh_RajmangalAuthor Commented:
I kept the name of the function the same, but changed the name of the module. I then deleted the expression used in the query and created it again using the same method. In the expression builder this time the module name had changed but the function name was the same.

"ambiguous name in expression" error often comes when you have two tables in the same query that have the same fieldname..

Is this the case here??

Then you need to change your query to use a fully-quallifiled name like customer.customerID instead of just filedname


Rajesh_RajmangalAuthor Commented:
Very strange,

Ive tried using the full name and changing the expression name to Expr4, but it still has the same error. the sql code is shown below:

SELECT HrsMinsSecs([CTC - Open Time]![Expr 1]) AS Expr4
FROM [CTC - Open Time];

any ideas?
Is the name of the field "Expr 1"??


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rajesh_RajmangalAuthor Commented:
Hi perove,

many thanks for your help, it was my error, i actually had another module (named different) but including the same function name. It worked after deleting the duplicate.

thanks again

ahhh..."somtimes we dont see the forest for all the trees"...

(norwegian saying..)

nave a nice day

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.