USer-Defined Function in query

[shragel]

Is there a way to creat user defined function for a access database when calling the datbase from vb


For example

Sub OpenRecordSet()
 
Set rst = dbs.OpenRecordSet("Select Field1, MyFunction(Field1) From Table1")
 
End Sub
 
Function MyFunction(AnyThing)
MyFunction = Anything + 1
End Function

[madjon]

You above example would work if you changed it to

Set rst = dbs.OpenRecordSet("Select Field1," & MyFunction(Field1) & "From Table1")

Although I am not sure that is a valid SQL statement. What exactly do you want to do?

[shragel]

Isn't there anyone here who understands the question?

[jhermiz]

Originally posted by shragel
Isn't there anyone here who understands the question?

No we are all stupid..

You cannot do that.

[jhermiz]

Originally posted by shragel
Isn't there anyone here who understands the question?

Why do you even have to do it like that...try this terd dropper:

public Sub OpenRS()
'error handling here...
 
dim i as Integer
 
i= Call SomeFunc()
 
db.OpenRecordset("SELECT * FROM Users WHERE IQ > " & i)
'other code

No reason to call it within your SQL.

Jon

[shragel]

Let Me Explain.

In Access When you go to Module and Paste the Following Code.

Function MyFunction(AnyThing)
MyFunction = Anything + 1
End Function

And then you go in to Query Design and put in sql ciew the following code

Select FieldNum, MyFunction(FieldNum) From Table1

You will get two columns. One column will have the value of FieldNum, And the other column will have the value of MyFunction(FieldNum) Which is FieldNum + 1.

So now the question, is there any way to do this in VB?

[szlamany]

Bill was nuts to think that ACCESS could be a SQL backend - that was before he realized how to really pilfer SYBASE...

Access with VBA - argghh...

Why not use real SQL, or at least MSDE?

The power is so much greater.

[frigginjerk]

vb does not have a direct connection with your mdb file, the way access does. vb uses rdo, dao, or ado for connections to a mdb.

with that being said, NO you cannot apply vb functions to a field via a SQL statement. There are some built-in SQL functions that are supported like SUM and AVERAGE but these are not vb functions they are SQL functions.

What you are asking for are only possible in Access and/or via stored procedures in a real DBMS.

If you open up the Access query builder you can see the available SQL functions.

[shragel]

Originally posted by frigginjerk
vb does not have a direct connection with your mdb file, the way access does. vb uses rdo, dao, or ado for connections to a mdb.

with that being said, NO you cannot apply vb functions to a field via a SQL statement. There are some built-in SQL functions that are supported like SUM and AVERAGE but these are not vb functions they are SQL functions.

Just for the record, You can use any vb function in your sql query to access. For example InStr() Or Right() Or Shell()

I Just dont know how to create UDF in VB.