Lookup SET QUOTED_INDENTIFIER in BOL

I think you'll have to probably do something along the lines of...

SET QUOTED_INDENTIFIER OFF
SELECT [Stock Code], [Intermediate Desciption],[Product Status] =
"Obsolete"
As Obsolete,[Production Line] FROM tblIntermediatesData UNION SELECT
[Stock
Code],[Stock Description],Obsolete,0 FROM [tblRaw Materials] WHERE
((([tblRaw Materials].[Stock Code]) Like "R*")) UNION SELECT * FROM
qryRecipeFP
SET QUOTED_INDENTIFIER ON


HTH

Barry

I am getting the following error when trying to select fields from 2 tables
and one query in a union query. ODBC--Call Failed [Microsoft][ODBC SQL
Server Driver][SQL Server]Incorrect syntax near '='. (#102). I have tried
running the query with each statement separate and each works but when I
union it together I get the error. I have also tried replacing the
"Obsolete" with 'Obsolete' and "R*" with 'R%' but still no joy

The SQL statement is:
SELECT [Stock Code], [Intermediate Desciption],[Product Status] = "Obsolete"
As Obsolete,[Production Line] FROM tblIntermediatesData UNION SELECT [Stock
Code],[Stock Description],Obsolete,0 FROM [tblRaw Materials] WHERE
((([tblRaw Materials].[Stock Code]) Like "R*")) UNION SELECT * FROM
qryRecipeFP;

Thanks in advance

[quoted text, click to view]

-- Begin SQL Statement
SELECT [Stock Code]
, [Intermediate Desciption]
,[Product Status] = "Obsolete" As Obsolete
,[Production Line]
FROM tblIntermediatesData

UNION

SELECT [Stock Code]
,[Stock Description]
,Obsolete
,0
FROM [tblRaw Materials]
WHERE ((([tblRaw Materials].[Stock Code]) Like "R*"))

UNION

SELECT * FROM qryRecipeFP
--End SQL Statement

Understanding what is happening is a lot easier if the command sequence is
formatted for readability.

First, I'd guess this is an invalid field name [Intermediate Desciption]
since 'Desciption' is misspelled.

Second,
[Product Status] = "Obsolete" As Obsolete
you can assign a column name using the AS keyword -OR- you can assign a
column name using the equals operator.

You cannot do both.
Either:
[Product Status] = "Obsolete"
or
"Obsolete" AS Obsolete

You may also (if you ever get this far), receive errors on this WHERE
clause:

WHERE ((([tblRaw Materials].[Stock Code]) Like "R*"))

because (a) SQL Server expects to see single quotes demarcing a string, and
(b) the correct wildcard parameter for this command is % not *

Remember, now that the data is on SQL Server, you're sending the SQL command
to the SQL Server, and the syntax needs to be SQL Server compliant, not
Access 97 compliant.

Finally, you really /should/ list the specific fields in your third SELECT
statement
SELECT * FROM qryRecipeFP

because the number of fields must be identical in all three SELECT
statements. If anybody ever changes the structure of that query (which SQL
interprets as a View), your command will blow up all over the place. And,
since we're only talking about four columns, it's really not that much
effort to list the four columns.

btw... all of the above is also tempered and qualified by what you're
actually writing the code in. Your subject line says "after moving data from
Access 97...", but I surely hope you're not trying to maintain the front-end
code in Access 97. At a minimum, I'd recommend migrating the front-end code
to Access XP, which is much more cooperative with SQL Server. Given that
it's now 2006, and the next version of Office is on the horizon, Access 2003
would be a better target.


[quoted text, click to view]