Converting Access Query To VB

[Capper]

I have an access query which looks something like this:

SELECT batting.player, careerteams(batting.player), stuff
FROM batting
WHERE batting.seg = 0

The careerteams is actually a function within access that returns back all the teams a player ever played for.

When I try to use the query in VB (regardless of where or how), it tells me its an invalid function call.

I've tried copying the function over to VB and having it be public in a module within my program, and still can't seem to get it to convert.

Is this possible with VB and I'm just missing something?

Thanks,
Chad

[dlm]

Hi

try something like this...

Dim MySQL as string

MySQL = "SELECT batting.player, " & careerteams(batting.player) & ", stuff FROM batting WHERE batting.seg = 0"

[paulw]

As far as I know, you cannot use User Defined Functions (UDFs) in this way in VB. The best way is to run a simple SELECT and then use the function on the resultant recordset.


e.g.
base a recordset, rs, say, on:
SELECT batting.player, "temp" As PlaceHolder, stuff FROM batting WHERE batting.seg = 0

With rs Do
Do Until .EOF
.Edit
!PlaceHolder = CareerTeams(!player)
.Update
.MoveNext
Loop
End With

Change for ADO and add error checking as necessary.

Cheers,

P.

[Capper]

Shortly after my posting, I found in a book that I couldn't use the function in a VB query. Thanks for the workaround suggestion. That was going to be my next question.

Chad