?
Solved

Using user-defined functions within a query

Posted on 2003-03-17
9
Medium Priority
?
1,034 Views
Last Modified: 2012-08-13
Hi,

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
Else
    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.
0
Comment
Question by:Rajesh_Rajmangal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 9

Expert Comment

by:perove
ID: 8150987
How do you use the function in the Query builder??

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

perove
0
 

Author Comment

by:Rajesh_Rajmangal
ID: 8151039
Hi,

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?
0
 
LVL 9

Expert Comment

by:perove
ID: 8151066
Did you change the name of the function?
Then you also have to (of course) change your query..

perove
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Rajesh_Rajmangal
ID: 8151081
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.
0
 
LVL 9

Expert Comment

by:perove
ID: 8151103
hmm..


"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
(tablename.filedname)

perove

 
0
 

Author Comment

by:Rajesh_Rajmangal
ID: 8151186
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?
0
 
LVL 9

Accepted Solution

by:
perove earned 280 total points
ID: 8151221
Is the name of the field "Expr 1"??

perove
0
 

Author Comment

by:Rajesh_Rajmangal
ID: 8151224
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

Raj
0
 
LVL 9

Expert Comment

by:perove
ID: 8151235
ahhh..."somtimes we dont see the forest for all the trees"...

(norwegian saying..)

nave a nice day

perove
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question