User function with dao and SQL in vb5

I don't remind whether it is possible to use a user defined function in a SQL sentence using DAO and VB5.
Can somebody help?

Best regards
H

[Mark Sreeves]

Please tell us a bit more!

------------------
Mark Sreeves
Analyst Programmer

[email protected]
A BMW Group Company

Lets say i defined in my module a function named "Myfunction".

Elsewhere in the same module i want to use it in the following code :
Mydb.openrecordset("Select Myfunction(Arg1) from table1")

I am using visual basic 5.0

Thanks for your help.
Best regards
H

[netSurfer]

I'm not sure if you can call a function in the middle like that but if you did, you'd have to do htis:

Mydb.openrecordset("Select " & Myfunction(Arg1) & " from table1")

You have to take the function part out of the quotes. If you can't do it that way (and you should be able to), just make a string equal to the MyFunction(Arg1) and put the string in.

------------------
'cos Buzby says so!'

Thanks you both for your help.

I forgot to tell that arg1 is the name of a field of my table.
Therefore your first proposal can not work.

What do you mean by " If you can't do it that way (and you should be able to), just make a string equal to the MyFunction(Arg1) and put the string in." ?

Best regards
H

[netSurfer]

I assumed that what you wanted to be returned from MyFunction would be the field name. It should work without a problem. Is MyFunction returning the field name? What is the purpose of MyFunction? Are you retrieving the field name from MyFunction or are you sending the info from the RecordSet into it?

If you are trying to retrieve info from MyFunction using Arg1, the code I gave you should work.

I guess I need more info on what MyFunction is doing and how you want it incorporated into the query.

------------------
'cos Buzby says so!'

[bsmith]

You can include a call to a function in the select statement just as you would a variable that you wanted to include.

    Mydb.openrecordset("Select " & Myfunction(Arg1) & " from table1")

Private Function Myfunction(Arg1 as String) As String
    ' enter your code here
    MyFunction = ...
End Function

Bsmith, you opened my eyes !

Many thanks
Arnaud

My table "table1" is made of a field named 'yyyymm' containing some month numbers like (199901,199902....). the type is dblong not dbdate.
I want to run a query which convert those number in letter as following : 199901 is 'J'
199902 is 'F', 199903 is 'M' and so folth.
In this way i wrote a function named "MyFuntion". It contains a select case.

So when i open my recordset by doing
Mydb.openrecordset("Select MyFunction(yyyymm) from table1") i would like this recordset contains("J","F","M".....) and not (199901,199902,199903....)

This is just an example.

Thanks for your support.
H

[netSurfer]

So,if I understand correctly, the data stored in the table is in

199702

format and you want to convert it as you open to make it J?

As far as I know you can't do it exactly the way you want. You would have to do this:

strMonth = MyFunction(recordset("Date"))

That would take the the date from the recordset and convert it.

If the data stored in the Database is stored as J etc, then the code that BSmith and I gave you would work. But it looks like you have the Numbers stored for the dates. The only thing I can suggest is getting them out and converting them afterwards.

Sorry can't be more help.

------------------
'cos Buzby says so!'

Thanks everybody, you answered my question.

Best regards
H

[bsmith]

You can use a Choose statement in your select statement if you are using MSAccess. There is also a case statment in SQL Server. There is a way to do what you want to do it depends on the database you have though.
This select statement I tested in MSAccess and it returned what you were looking for.

SELECT Choose(MID([tbl2].[Field2],5,2),"J","F","M","A","Y","U","L")
FROM tbl2;