SQL and VB User Functions

[dw85745]

User Function fails in an SQL Select statement within VB.

User function works great with SQL Select statement within Access when User Function is included within an Access module.

ANYONE KNOW HOW TO SOLVE THIS?

[Franky]

explain a little more. Normaly all SQl statements that are working in access hould be work in VB.

Franky

[dw85745]

Thanks for responding.

FYI: In doing initial search before post JasonS had this problem a while back but saw no solution posted. Hence repost.
He also has an example if you wish to review it.

--------------

The following SQL was created. WeekEnding is a User created functions. The SQL and function return the correct recordset in Access.

In VB it executes but ZERO records are returned.

So it appears there is some issue or special format necessary so Access can recognize the User Function when passed via an SQL string. BTW: I have included the User function both in Access and VB.



'******
'Create Query To Compress
'Daily to Weekly Data
'******
strParm = "PARAMETERS [pBegDate] DateTime, [pEndDate] DateTime; "
strSQL = strParm & "SELECT (WeekEnding([fldHistDateTime])) As DateOnly, " _
& "First(fldHistOpen) AS OpenPrice, " _
& "Max(fldHistHigh) AS HighPrice, " _
& "Min(fldHistLow) AS LowPrice, " _
& "Last(fldHistClose) AS ClosePrice " _
& "FROM " & strSourceTBLName & " " _
& "WHERE (((fldHistDateTime) Between [pBegDate] And [pEndDate])) " _
& "GROUP BY (WeekEnding([fldHistDateTime]));"