Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using user-defined functions within a query

Posted on 2003-03-17
9
Medium Priority
?
1,039 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
  • 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

580 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