Incorrect Syntax Near '=' 1

[rwn]

I keep getting this syntax error for this expression below. The expression is running on a SQL database.

SELECT Job_Operation.Job, Job_Operation.Job_Operation, Job_Operation.Sequence, Material_Req.Material, Material_Req.Pick_Buy_Indicator, Material_Req.Status, IIf([job_operation].[sequence] = 2 And [material_req].[status]='O',"MATERIAL NOT PICKED") AS Expr1
FROM Job_Operation INNER JOIN Material_Req ON Job_Operation.Job = Material_Req.Job
WHERE (((Job_Operation.Sequence ) =2));

 

[kray4660]

I do not believe IIF is a SQL function. I believe it is an Access function.

 

[lbass]

Maybe try using a "case when" statement in place of the iif().

-LB

 

[rwn]

IBASS, I just had time to view the response. Thank you! for this expression below that works in access how can I use the "CASE WHEN"

SELECT Job_Operation.Job, Job_Operation.Sequence, Material_Req.Material, Material_Req.Pick_Buy_Indicator, Material_Req.Status, IIf([job_operation].[sequence]=1 And [job_operation].[status]="C" And [material_req].[status]="O","MATERIAL NOT PICKED") AS Expr1, Job_Operation.Status
FROM Job_Operation INNER JOIN Material_Req ON Job_Operation.Job = Material_Req.Job
WHERE (((IIf([job_operation].[sequence]=1 And [job_operation].[status]="C" And [material_req].[status]="O","MATERIAL NOT PICKED"))="MATERIAL NOT PICKED"));

 

[lbass]

SELECT Job_Operation.Job,
Job_Operation.Sequence,
Material_Req.Material,
Material_Req.Pick_Buy_Indicator,
Material_Req.Status,
(case when [job_operation].[sequence]=1 And
[job_operation].[status]="C" And
[material_req].[status]="O" then 'MATERIAL NOT PICKED' end) AS Expr1,
Job_Operation.Status

FROM Job_Operation
INNER JOIN Material_Req ON Job_Operation.Job = Material_Req.Job

WHERE [job_operation].[sequence]=1 And
[job_operation].[status]="C" And
[material_req].[status]="O"

I'm not sure if this is the correct syntax for SQL, but it works for Oracle. In the where clause you don't really need the case when since the criteria will return only those records that you are labelling 'not picked'. Of course, then really you don't need the expression as a field, since all records will be 'not picked' so you could just add a text box.

-LB