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

Query that works in 2007 does not work in 2002

P: n/a
I have this query that when run on Access 2007 works, when run in
2002
it fails with the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server] Incorrect Syntax near
'.'.
INSERT INTO tblQualitySurvey ( Company, Job_Num, QS_Date, First_Name,
Customer_Number, Email, Job_Num_Count ) SELECT DISTINCTROW
Customers.Company, First(tblJobs.Job_Num) AS FirstOfJob_Num,
First(#10/8/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
(((Customers.EMail IS NOT NULL) AND ((tblJobs.Confirmed)='Yes') AND
((tblJobs.StrikeCall) #10/1/2007# And (tblJobs.StrikeCall) <=
#10/8/2007#)) GROUP BY Customers.Company, Customers.[First Name],
Customers.[Customer Number], Customers.Email WITH OWNERACCESS OPTION;
I know # should not work for SQL server, but it does on the 2007
machine.
The tblQualitySurvey is on SQL Server and the Customers and tblJobs
are local jet tables.
Any ideas?
Preston
Dec 10 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Sun, 9 Dec 2007 16:41:08 -0800 (PST), pm********@gmail.com wrote:

My money is on:
tblJobs.Confirmed='Yes'
I would write that as
tblJobs.Confirmed<>0

But of course you can simplify the query until you find the offending
clause yourself.

-Tom.

>I have this query that when run on Access 2007 works, when run in
2002
it fails with the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server] Incorrect Syntax near
'.'.
INSERT INTO tblQualitySurvey ( Company, Job_Num, QS_Date, First_Name,
Customer_Number, Email, Job_Num_Count ) SELECT DISTINCTROW
Customers.Company, First(tblJobs.Job_Num) AS FirstOfJob_Num,
First(#10/8/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
(((Customers.EMail IS NOT NULL) AND ((tblJobs.Confirmed)='Yes') AND
((tblJobs.StrikeCall) #10/1/2007# And (tblJobs.StrikeCall) <=
#10/8/2007#)) GROUP BY Customers.Company, Customers.[First Name],
Customers.[Customer Number], Customers.Email WITH OWNERACCESS OPTION;
I know # should not work for SQL server, but it does on the 2007
machine.
The tblQualitySurvey is on SQL Server and the Customers and tblJobs
are local jet tables.
Any ideas?
Preston
Dec 10 '07 #2

P: n/a
On Dec 10, 8:40 am, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Sun, 9 Dec 2007 16:41:08 -0800 (PST), pmacdid...@gmail.com wrote:

My money is on:
tblJobs.Confirmed='Yes'
I would write that as
tblJobs.Confirmed<>0

But of course you can simplify the query until you find the offending
clause yourself.

-Tom.
I have this query that when run on Access 2007 works, when run in
2002
it fails with the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server] Incorrect Syntax near
'.'.
INSERT INTO tblQualitySurvey ( Company, Job_Num, QS_Date, First_Name,
Customer_Number, Email, Job_Num_Count ) SELECT DISTINCTROW
Customers.Company, First(tblJobs.Job_Num) AS FirstOfJob_Num,
First(#10/8/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
(((Customers.EMail IS NOT NULL) AND ((tblJobs.Confirmed)='Yes') AND
((tblJobs.StrikeCall) #10/1/2007# And (tblJobs.StrikeCall) <=
#10/8/2007#)) GROUP BY Customers.Company, Customers.[First Name],
Customers.[Customer Number], Customers.Email WITH OWNERACCESS OPTION;
I know # should not work for SQL server, but it does on the 2007
machine.
The tblQualitySurvey is on SQL Server and the Customers and tblJobs
are local jet tables.
Any ideas?
Preston- Hide quoted text -

- Show quoted text -
Good ideas on simplifying. The field really is text, so 'Yes' is the
correct syntax. Thanks
Dec 10 '07 #3

P: n/a
On Sun, 9 Dec 2007 16:41:08 -0800 (PST), pm********@gmail.com wrote:
>I have this query that when run on Access 2007 works, when run in
2002 it fails with the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server] Incorrect Syntax near
'.'.
>INSERT INTO tblQualitySurvey ( Company, Job_Num, QS_Date, First_Name,
Customer_Number, Email, Job_Num_Count ) SELECT DISTINCTROW
Customers.Company, First(tblJobs.Job_Num) AS FirstOfJob_Num,
First(#10/8/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
(((Customers.EMail IS NOT NULL) AND ((tblJobs.Confirmed)='Yes') AND
((tblJobs.StrikeCall) #10/1/2007# And (tblJobs.StrikeCall) <=
#10/8/2007#)) GROUP BY Customers.Company, Customers.[First Name],
Customers.[Customer Number], Customers.Email WITH OWNERACCESS OPTION;
>I know # should not work for SQL server, but it does on the 2007
machine.
The tblQualitySurvey is on SQL Server and the Customers and tblJobs
are local jet tables.
The #'s are ok as long as the query is run inside of Access and it's
not a pass-thru query. (I know you know this Preston, I'm mentioning
it for the completeness of the thread.)

The error probably means that it's a formatting error in the SQL text,
meaning that there's a cartridge return in the text that shouldn't be
there.
Try cutting and pasting the (re-formatted) query below and see if that
works. Please note that there should be a space at the end of each
line in the text. (I also aliased the tables to make the SQL text as
short as possible to avoid word wrap.)

For what it's worth, you probably don't need the "DISTINCTROW" since
this is already a grouping query.

INSERT INTO tblQualitySurvey ( Company,
Job_Num,
QS_Date,
First_Name,
Customer_Number,
Email,
Job_Num_Count )
SELECT DISTINCTROW C.Company,
First(J.Job_Num) AS FirstOfJob_Num,
First(#10/8/2007#) AS Expr1,
C.[First Name],
C.[Customer Number],
C.Email,
Count(J.Job_Num) AS CountOfJob_Num
FROM Customers AS C
INNER JOIN tblJobs AS J
ON C.[Customer Number] = J.[Customer Number]
Where C.EMail IS NOT NULL
AND J.Confirmer='Yes'
AND (J.StrikeCall #10/1/2007#
And J.StrikeCall <= #10/8/2007#)
GROUP BY C.Company,
C.[First Name],
C.[Customer Number],
C.Email
WITH OWNERACCESS OPTION
---
Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!
Dec 13 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.