Results 1 to 5 of 5
  1. #1
    altemir is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    31

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


    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?
    Last edited by June7; 02-28-2012 at 05:52 PM. Reason: moderator edit typo

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,354
    Maybe because the inner join doesn't have link on pk/fk fields.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Apr 2012
    Location
    Los Angeles
    Posts
    75
    Try using TRUE instead of -1

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

  4. #4
    Join Date
    Apr 2012
    Location
    Los Angeles
    Posts
    75
    Also it looks like you are missing the ON statement for your join.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,427
    Code:
    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
    Code:
    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??
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Insert statement
    By crowegreg in forum Programming
    Replies: 2
    Last Post: 08-19-2011, 02:20 PM
  2. Help about access INSERT INTO statement
    By ducthang88 in forum Programming
    Replies: 7
    Last Post: 12-03-2010, 08:40 AM
  3. Replies: 4
    Last Post: 09-03-2010, 02:55 PM
  4. Replies: 1
    Last Post: 05-13-2010, 10:37 AM
  5. Syntax Error In Insert Into Statement
    By frankvfox in forum Queries
    Replies: 1
    Last Post: 09-09-2008, 12:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums