>The query is completely wrong for SQL Server. IIF is Access function and you also seem to put it in a strange place. If you want to filter the SUM, in
>SQL Server you put it in HAVING condition, etc.


Naomi, you may have missed this in Alex's first post, but he *is* using Access, not SQL Server.

~~Bonnie



>>HI, ALL
>> I don't konw my sql statement have what problem? And how do solve?
>>i use Access, i check the problem is in sum(datediff('yyyy','01/01/' & w.from_year ,'01/01/' & w.to_year )) >= 1, but i don't know
>>

>>select distinct (c.candidate_no)  FROM ((candidate as c left JOIN education as e ON  c.candidate_no = e.candidate_no )
> left Join working as w ON c.candidate_no = w.candidate_no )
>
>group by c.candidate_no,c.last_name ,c.nick_name,
> IIf( c.dob = '  /  /',0,DATEDIFF('yyyy',c.dob,now) ) , -- problem here - SQL Server doesn't have empty dates and uses CASE instead of IIF
>
>c.mobile,c.expect_salary,
>c.available_option,e.level,e.institute,w.Industry,w.work_position,w.place,
>w.employer,w.duty,w.Achievement 
>having 1 = 1 and sum(datediff('yyyy','01/01/' & w.from_year ,'01/01/' & w.to_year )) >= 1
>>
>>
>>

>
>The query is completely wrong for SQL Server. IIF is Access function and you also seem to put it in a strange place. If you want to filter the SUM, in
>SQL Server you put it in HAVING condition, etc.
>
>Can you post your tables definitions, some input and desired output, so it will be much easier to write a correct SQL Server query then correct this mess (I don't think it will work in Access this way also).