works in query window, but not in code

[pmacdiddie]

I have this sql that comes from the debug screen... where it fails in
code. [Microsoft][ODBC SQL Server Driver][SQL Server] 'First' is not
a recognized function name. I had this running on another machine
where it did not question the syntax.
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;

When I paste it into a query window it runs fine. Any ideas? Does
the query window parse it differently for SQLServer?
developer_tblQualitySurvey is attached SQLServer table and tblJobs and
Customers are attached Jet tables.
Thanks, Preston

[Tom van Stiphout]

On Mon, 14 Jan 2008 12:58:16 -0800 (PST), pm********@gmail.com wrote:

It appears the statement is passed to SQL Server for evaluation, and
as you may know T-SQL does not know about First as an aggregate
function. Try rewriting using the Min function.
SQL Server also does not know about WITH OWNERACCESS OPTION.

-Tom.

>I have this sql that comes from the debug screen... where it fails in
code. [Microsoft][ODBC SQL Server Driver][SQL Server] 'First' is not
a recognized function name. I had this running on another machine
where it did not question the syntax.
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;

When I paste it into a query window it runs fine. Any ideas? Does
the query window parse it differently for SQLServer?
developer_tblQualitySurvey is attached SQLServer table and tblJobs and
Customers are attached Jet tables.
Thanks, Preston

[pmacdiddie]

On Jan 14, 10:13*pm, Tom van Stiphout <[e-mail]:

On Mon, 14 Jan 2008 12:58:16 -0800 (PST), [e-mail] wrote:

Ok, so i dumped the first and replaced with Min. Also deleted with
owner access. That helped. Thanks

Now the problem is with the # in the dates. Are there some other
drivers to use that will parse the query before it gets to the SQL
server.

As I mentioned this was running ok on Access 2007, it is now in Access
XP, where it does not work.

-Preston