Outer Join query not working in Microsoft Access 2000 (9.0.3821 SR -1

[ganeshrvs]

Microsoft Access 2000 - Outer Join query not working in Microsoft Access 2000 (9.0.3821 SR -1 ).

I have two tables. 1. Step_Level_Result 2. Test_Step.

Step_Level_Result has ,

MeasureId
Step_Code
Step_Result

Test_Step has,

Step_Code
Language_Id
Step_Desc.

Test_Step table is a master table and Step_Level_Result is a transaction table. Step_code is the key. Test_Step table has Step Descriptions in different languages. The data in Test_Step will be like the following,

Step_Code Language_ID Step_Desc
101 99 desc101_99
101 03 desc101_03
101 04 desc101_02
102 99 desc102_99

99 is the default language id. Each step code will have description in 99. Some step codes will have descriptions also in other languages.

I need a query, which should get the language id as input, display the Step_Level_Result info with the Step_description for the selected language. If the query returns four step codes and if two code has description in the supplied language and other two do not have the description in the selected language, then the Query should display the description in 99 for the that two.

I have written a query, which is working correctly in Microsoft Access 2000 (9.0.2720). But the same is not working in Microsoft Access 2000 (9.0.3821 SR -1 ).

My query is

SELECT
IIF(isnull(TS1.Step_Desc),TS.Step_desc,TS1.Step_desc) AS Step_Desc,
TRSL.Step_Result,
TRSL.Upper_limit,
TRSL.Lower_limit,
TRSL.Service_Record,
TRM.Test_Date,
TRM.Measure_ID
FROM Test_Results_Measurements AS TRM Inner join (Test_Results_Step_Level As TRSL left join (Test_step As TS left join Test_Step AS TS1 on (TS.Step_Code=TS1.Step_Code and TS1.Language_ID='03'))
on (TRSL.step_code=TS.Step_code and TS.Language_Id='99'))
on TRM.measure_id=TRSL.measure_id
WHERE (TRM.Measure_Id='FR0000000088000003') ORDER BY TRSL.Measure_ID,TRSL.Step_Row_ID ;

Can you please help me to solve the problem.