You need to use a "Where" phrase with like

SELECT Myfield from .....
WHERE (Myfield Like 'ab*')


whre ab is the 1st two character

"michdenis" wrote:

i have tried numerous syntax and i was unable to get a
result if i use "replace" as the function in my query.

I always get the same message saying ... this function
is not known...

Thanks for your collaboration.


"Joel" a écrit dans le message de groupe de discussion :
...
The command text portion of a query is the SQL. The SQL is simply a string
(String Query Language) Below I set a variable SQL to yoour query string.

SQL = "SELECT Left(MyField,2) From..."

Then you can make a variable FUNC

FUNC = "Left"
SQL = "SELECT " & FUNC" & "(MyField,2) From..."


or
FUNC = "Replace"
SQL = "SELECT " & FUNC" & "(MyField,2) From..."



"michdenis" wrote:

Back to my first message :
this example works :

SELECT Left(MyField,2) From...

Suppose you want to change Left() for Replace() in this query
Is it possible ? You see a big difference between both ?
And it's not even a UDF...




"Joel" a écrit dans le message de groupe de discussion
:
...
Now I'm confused!!!

i want to call a user defined fonction in a query


???
This sounds like you want to perform addtional filtering. Instead of
performing a query why not open a recordset and perform your functions to the
recordset.


"michdenis" wrote:

Thanks for your explanation and your time.

but i did not totally understand !

'-----------------
A UDF function will only get called if a cell that has a
| dependency to the UDF gets changed.
***OK

| So if you are only calling this UDF infrequently then it
| is acceptable to to put the query into a UDF.
*** i want to call a user defined fonction in a query
not put the query into a UDF
if we can't, it was my question ...

| If you have hundreds of these queries then it will take
| a long time to open the workbook because opening
| the workbook will update all the queries.
'-----------------
**** Ok, i was using a sheet in the same workbook for conducting my test.
When a connexion is established to a workbook, this connexion may
and can support as many queries as required to the same database till
the connexion is closed. isn't it ?

You did a helpful intervention. Thank.




Each time the function is called a connection will be made to the database.
The connection is similar to opening the file that it has to transverse the
file system to find the file and look inside the file without opening the
file. Opening a file usually requires moving the data in the file into
memory on your PC. A connection doesn't have to move the data onto the PC.

1) Reading the data from access is the quickest
2) A connection from excel is slower than getting data directly from an
Access macro
3) Opening the database from excel is the slowest method.

"michdenis" wrote:

Hi Joel,

You comment about the time required to perform the task...

First of all, can we used a user function ? Any example to show ?
Do you know why is possible to do this in Access and not in Excel ?
How about a web site, a tutorial discusssing about this subject ? Any suggestion ?

Thanks for your participation.




"Joel" a écrit dans le message de groupe de
discussion
:
...
I'snt this going to be slow? It means every time you modify the cell a new
connection has to be established. Would it be better to perform a query
returning all the fields, then lookup the value you need in the query results.

"michdenis" wrote:

Hi Patrick,

| wasn't this an earlier question? I responded that I wasn't able to do this :(
| but I'm sure that there's a way to do it.

Personally, it's the first time i ask this question.

Someone of this group gave this exemple... and it works too

"SELECT DISTINCT Sum([MyField]) AS SumofMyField FROM...

But how about a user fonction ? And more generally speaking
what functions are available in ADO for a Query in an Excel environment ?

Thanks.



"michdenis" wrote:

Hi,

Is it possible to employ a user fonction in a query with ADO in Excel ?

If yes, is it possible to get a few examples which syntax should i used ?

For example, this one works :
SELECT Left(MyField,2) From .... But i can't integrate a personal fonction !

Any suggestion about a web site discussing of this subject is welcome.

Thanks for your time and your collaboration.