By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 906 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

works in query window, but not in code

P: n/a
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

Jan 14 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Jan 15 '08 #2

P: n/a
On Jan 14, 10:13*pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Mon, 14 Jan 2008 12:58:16 -0800 (PST), pmacdid...@gmail.com 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
Jan 15 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.