Converting SQL Server 2000 query to MS Access 2000 query

[Stefan V.]

Hello!
I am trying to convert a query written for SQL Server 2000 database
tables, to a MS Access query.
Here is what I have in SQL Server:

SELECT t2.*,
CASE WHEN t2.QType = '3' THEN t1.Note ELSE CASE WHEN t2.QType = '2'
THEN
CASE WHEN CONVERT(varchar(100), t1.ANumber) = '1' THEN 'Yes' ELSE 'No'
END ELSE CASE WHEN CONVERT(varchar(5), t2.Qnumber)+'.' +
CONVERT(varchar(5), t1.ANumber)
= t2.SubQ THEN 'Selected' ELSE 'Not Selected'
END END END
AS Client_Response
FROM (SELECT * FROM ClientAnswer
WHERE (OrganizationID = '491') AND (InstrumentId = 'CM005')) AS
t1
RIGHT JOIN
(SELECT Questions.InstrumentId, Questions.Qnumber,
Questions.Question,
Questions.QType,
CASE WHEN TEMPT.A IS NULL THEN QNumber ELSE TEMPT.A END AS
SubQ,
CASE WHEN TEMPT.Answer IS NULL THEN Questions.Question ELSE
TEMPT.Answer END AS Answer
FROM Questions LEFT OUTER JOIN
(SELECT QNumber AS Q, Answer, CONVERT(varchar(5), QNumber)
+ '.' +
CONVERT(varchar(5), ANumber) AS A
FROM Answers
WHERE InstrumentId = 'CM005')) TEMPT
ON Questions.Qnumber = TEMPT.Q
WHERE (Questions.InstrumentId = 'CM005')) AS t2
ON t1.QNumber = CASE WHEN t2.QType <> '1' THEN t2.Subq ELSE t2.QNumber
END
ORDER BY subq
It runs perfectly on the SQL Server, however it keeps giving me
"Invalid use of null" or "The statement is not written properly or is
too complex.." errors.
Here is the Access statement I am trying to run:

SELECT IIF(cstr(t2.QType) = '3', t1.Note, IIF(cstr(t2.QType) =
'2', IIF( cstr(t1.ANumber) = '1','Yes','No'), IIF( cstr(t2.Qnumber) +
'.' + cstr(t1.ANumber) = t2.SubQ, 'Selected', 'Not Selected')))
AS Client_Response
FROM (SELECT * FROM ClientAnswer
WHERE (cstr(OrganizationID) = '491') AND (InstrumentId = 'CM005')) AS
t1
RIGHT JOIN
(SELECT Questions.InstrumentId, Questions.Qnumber, Questions.Question,
Questions.QType, iif(TEMPT.A = NULL, QNumber, TEMPT.A) AS SubQ,
iif(TEMPT.Answer = NULL, Questions.Question, TEMPT.Answer) as Answer
FROM Questions
LEFT OUTER JOIN
(SELECT QNumber AS Q, Answer, cstr(QNumber) + '.' + cstr(ANumber) AS A
FROM Answers WHERE (InstrumentId = 'CM005')) TEMPT ON
Questions.Qnumber = TEMPT.Q
WHERE (Questions.InstrumentId = 'CM005'))
AS t2 ON t1.QNumber = iif(t2.QType <> '1', t2.Subq, t2.QNumber)
ORDER BY subq

I HATE ACCESS!!!
Please help...

[John Winterbottom]

"Stefan V." <st************@hotmail.com> wrote in message
news:1c**************************@posting.google.c om...

Hello!
I am trying to convert a query written for SQL Server 2000 database
tables, to a MS Access query.
Here is what I have in SQL Server:

SELECT t2.*,
CASE WHEN t2.QType = '3' THEN t1.Note ELSE CASE WHEN t2.QType = '2'
THEN
CASE WHEN CONVERT(varchar(100), t1.ANumber) = '1' THEN 'Yes' ELSE 'No'
END ELSE CASE WHEN CONVERT(varchar(5), t2.Qnumber)+'.' +
CONVERT(varchar(5), t1.ANumber)
= t2.SubQ THEN 'Selected' ELSE 'Not Selected'
END END END
AS Client_Response
FROM (SELECT * FROM ClientAnswer
WHERE (OrganizationID = '491') AND (InstrumentId = 'CM005')) AS
t1
RIGHT JOIN
(SELECT Questions.InstrumentId, Questions.Qnumber,
Questions.Question,
Questions.QType,
CASE WHEN TEMPT.A IS NULL THEN QNumber ELSE TEMPT.A END AS
SubQ,
CASE WHEN TEMPT.Answer IS NULL THEN Questions.Question ELSE
TEMPT.Answer END AS Answer
FROM Questions LEFT OUTER JOIN
(SELECT QNumber AS Q, Answer, CONVERT(varchar(5), QNumber)
+ '.' +
CONVERT(varchar(5), ANumber) AS A
FROM Answers
WHERE InstrumentId = 'CM005')) TEMPT
ON Questions.Qnumber = TEMPT.Q
WHERE (Questions.InstrumentId = 'CM005')) AS t2
ON t1.QNumber = CASE WHEN t2.QType <> '1' THEN t2.Subq ELSE t2.QNumber
END
ORDER BY subq

1. Format the original statement with line breaks and indentations, (easy to
do in Query Analyzer). It makes the code much easier to read - for us as
well as you.
2. Use IIf or the Switch statement, (depending on number of conditaions)
instead of CASE .. WHEN .. ELSE
3. Use CLng to convert text to numeric datatype and CStr to go the other way
4. CASE .. WHEN IS NULL THEN can be replaced by COALESCE in sql server and
Nz() in Access (Jet)
5. Use RIGHT OUTER join instead of RIGHT JOIN (I'm not 100% sure but IU
don't think you can use RIGHT JOIN in Access)
And some general notes
6. It looks like the first CASE statement can be simplified - case gives you
a choice between 1, 2, 3 .. n options - you don't have to embed multiple
case statetements as you would do with IIf.
7. Why do you alias some tables and not others?
8. SELECT t2.* is not good sql programming - it's better to explicitly
declare all your columns.
..