Use vba function in a query

Question

Probably a simple query but would very much appreciate help on the following.

I have a table with a column of data “Heritage” from which I wish to manipulate the string characters and get the first thee letters of each record.

How do I call a simple vba function that does this from the Design view of the Query in Access.

I have a simple public function as below

Public Function Get_Letters(Heritage As String) As String

            Get_Letters = Left(Heritage, 3)

End Function

I call this from the Query in design view as “Name: Get_Letters([Heritage])

When I run this I get “Undefined Function Get_Letters in expression.” error

Any advice will be very gratefully received

Many thanks

spike

Answer 1

Why are you bothering? Why not just use:

Alias: Left([Heritage],3)

as a column in your query?

Answer 2

Make sure that the name of the module containing the function "Get_Letters" is not also "Get_Letters".  That's one possible cause of this problem.  The name of a module can't be the same as the names of procedures it contains.

Answer 3

Thank you both.

yes i guess you are right "Name" is a reserved word i changed it to

"Days: Get_Letters([Heritage])" 

and it still does not work.  It is in a standard module not a Class Module

any further advice would be gratefully received

Thanks

spike

Answer 4

Hi,

I tried your function and it work well

SELECT FTAF.CXAF, get_Letters([CXAF]) AS Espr1

FROM FTAF;

Try to change the word "Name" in your statement, I think that that's a reserverd word.

Bye Mimmo

Answer 5

Make sure that you store the function in a standard module (the kind that you create by selecting Insert > Module in the Visual Basic Editor). It won't work if you store it in a class module (such as the module belonging to a form or report)