How to make self-define function in SQL to work in VB with MS Access

I use MS Access to create a database, then I create a module and a
function in there. Then I try to use that function in the query and it
works fine.

Eg: SELECT * FROM myTable WHERE myFunction(myFieldInTable)

It works fine ... the result is displayed as expected.

But when I convert it to MS Access 97 format ... then I use VB and add
in a DataGrid and ADO control, then the ADO control's datasource ... I
set it to the query like above, it fail. It give me an error like
"undefined function 'myFunction' in expression".

Is there any solution I can do with that?


======================================================
Actually this is my situation where I need such a self-define function
in the query. I got a number, says "123" in the "NUMBER" field in the
table "NUMBER_TABLE", then I want a query like:

"SELECT * FROM NUMBER_TABLE WHERE combination(NUMBER, 312)"

will return the record with "123" in the "NUMBER" field. The
'combination(xxx, yyy)' will actually make any possible combination of
the "xxx" and match it with "yyy", if the one of the combination match
"yyy", then it will return true. Eg: 123, 132, 213, 132, 312, 321 ...
will be used to match with the number 312, then it will return true
and make the query return the record "123".

It works when I execute that query in MS Access 2000.
But fail when I convert it to MS Access 97 format and use it with the
ADO and Datagrid control.
======================================================

Bob
Sat Dec 04 06:12:40 CST 2004


Peter CCH wrote:
> I use MS Access to create a database, then I create a module and a
> function in there. Then I try to use that function in the query and it
> works fine.
>
> Eg: SELECT * FROM myTable WHERE myFunction(myFieldInTable)
>
> It works fine ... the result is displayed as expected.
>
> But when I convert it to MS Access 97 format ... then I use VB and add
> in a DataGrid and ADO control, then the ADO control's datasource ... I
> set it to the query like above, it fail. It give me an error like
> "undefined function 'myFunction' in expression".
>

You cannot run procedures in an Access VBA module from external programs.
This is one of the limitations of Access that developers have been
complaining about for 10+ years. You will need to come up with another way
to solve your problem (or migrate your app to SQL 2000, where user-defined
functions are usable).


> ======================================================
> Actually this is my situation where I need such a self-define function
> in the query. I got a number, says "123" in the "NUMBER" field in the
> table "NUMBER_TABLE", then I want a query like:
>
> "SELECT * FROM NUMBER_TABLE WHERE combination(NUMBER, 312)"
>
> will return the record with "123" in the "NUMBER" field. The
> 'combination(xxx, yyy)' will actually make any possible combination of
> the "xxx" and match it with "yyy", if the one of the combination match
> "yyy", then it will return true. Eg: 123, 132, 213, 132, 312, 321 ...
> will be used to match with the number 312, then it will return true
> and make the query return the record "123".
>

Your explanation is a little confusing. I think this is what you mean: given
a table with a column called number containing the following data:

415
123
564
768
213

How can I create a query to return these two rows:
123
213

when provided with "312" as the criterion? Those two rows should be returned
because they are two of the possible combinations resulting from "312".

Is that correct? Are you limiting te data entered in this column to 3
characters? If so, then I would create another table called tmpCriteria with
a single column called "criteria". From your application, I would write code
to generate the insert statements to insert the possible combinations of the
supplied criterion into this table. So that it would result in:

Criteria
123
132
213
231
321
312


This will allow you to use an inner join to return the correct records:

select <column list - don't use *> from
NUMBER_TABLE n INNER JOIN tmpCriteria c
ON n.Numbers = c.Criteria

Then simply clear the tmpCriteria table.


Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"