Can't INSERT into SQL Server Table using an IIf Statement

[altemir]

I have an INSERT statement that I'm executing from Access 2010 on a linked table in SQL Server. It goes like this:



INSERT INTO TableA (JobStatus) SELECT ' " & IIf(PrintFlag = -1, "In Work", "Pending") & " ' FROM TableA INNER JOIN TableB"
DoCmd.RunSQL strSQL

The problem is that a JobStatus of "In Work" is never getting assigned even if PrintFlag = -1. Note that TableA is an SQL Server linked table and TableB is a local table in Access.

Any reason this wouldn't work?

[June7]

Maybe because the inner join doesn't have link on pk/fk fields.

[AccessMSSQL]

Try using TRUE instead of -1

INSERT INTO TableA (JobStatus) SELECT ' " & IIf(PrintFlag = True, "In Work", "Pending") & " ' FROM TableA INNER JOIN TableB"

[AccessMSSQL]

Also it looks like you are missing the ON statement for your join.

[ssanfu]

INSERT INTO TableA (JobStatus) SELECT ' " & IIf(PrintFlag = True,  "In Work", "Pending") & " ' FROM TableA INNER JOIN TableB"

Are "In Work" and "Pending" field names? Looking at the select part, if PrintFlag is TRUE, wouldn't the SQL look like

INSERT INTO TableA (JobStatus) SELECT 'In Work' FROM TableA INNER JOIN  TableB"

???
That doesn't make sense..

Unless you are trying to update [Jogstatus] to "In Work", but then you wouldn't need the sub query??