local / SQL conflict

[pmacdiddie]

I am having trouble getting this query to run in code. It fails with
the following error [Microsoft][ODBC SQL Server Driver][SQL Server]
'First' is not a recognized function name. The tblQualitySurvey is
on SQL Server, tblJobs and Customers are jet tables on a server on
property. All tables are attached.

When I paste the SQL string from the debug window into a query window,
it runs fine. By the way, I had this running on another machine where
it did not question the syntax.


cnn.Open "Driver={SQL
Server};Server=sqla15.zbaenter.com;Database=spi2000;Uid=dev;Pwd=password"

Dim strSQL As String

INSERT INTO developer_tblQualitySurvey ( Company, Job_Num, QS_Date,
First_Name, Customer_Number, Email, Job_Num_Count ) SELECT
Customers.Company, First(tblJobs.Job_Num) AS FirstOfJob_Num,
First(#12/24/2007#) AS Expr1, Customers.[First Name], Customers.
[Customer Number], Customers.Email, Count(tblJobs.Job_Num) AS
CountOfJob_Num FROM Customers INNER JOIN tblJobs ON Customers.
[Customer Number] = tblJobs.[Customer Number] Where
(((tblJobs.Confirmed)='Yes') AND ((tblJobs.StrikeCall) > #12/17/2007#
And (tblJobs.StrikeCall) <= #12/24/2007#)) GROUP BY
Customers.Company,
Customers.[First Name], Customers.[Customer Number], Customers.Email
HAVING(((Customers.EMail) Is Not Null)) WITH OWNERACCESS OPTION;




Thanks, Preston

 

[Brendan Reynolds]

There's no built-in function called "First" in T-SQL. Is it a custom
function? If so, you may need to specify the owner of the function, e.g.
"dbo.First" if dbo is the owner of the function.