Function results not read by others

[Andrew Ofthesong]

Hi, whenever I make a query, with a field that is the result of a custom
formula

(example: "Select field1, MyFunc(Field1) As Field 2 from Table1)

i can see the results perfectly in Access but when importing data from
SQLServer, or Excel (or any other) i get an "the function 'MyFunc' is not
defined in the expresion"..

How can i do it?

Thanks a lot!

 

[Tim Ferguson]

I can see the results perfectly in Access but when importing data from
SQLServer, or Excel (or any other) i get an "the function 'MyFunc' is not
defined in the expresion"..

Because when Access processes a query in Jet, it can use its own knowledge
of modules and VBA and some black magic to intercept the results.

In any other scenario, however, the SQL engine uses the database directly
and has no access to any of the GUI components, VBA included.

You'll have to recompose the function in SQL. If you are using SQL server
or MSDE, you can build very complex expressions using a View or a SP. In
Excel, it is easy to prepare the data for transfer using its own expression
handlers.

HTH


Tim F

 

[Andrew Ofthesong]

Thanks Tim...

What is a "SP"?... and "MSDE"

in sqlserver i can make "select-case", and "convert" in a view and queries,
but i havn't been able to do this in access