|
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...
| 
