Results 1 to 3 of 3

Thread: SQL help

  1. #1
    Join Date
    Jun 2011
    Posts
    5

    Unanswered: SQL help

    Hi

    I am new to SQL and need some help, I am Currently getting the error "Number of query values and output fields are different"

    Also can I have the IIf statements in like I have done?

    Thanks in advance,

    Andy

    Code:
    INSERT INTO 
    [Job Details 1] (
    [Position],  
     [Title],
     [Effective ^dd/mm/yyyy],
     [Status],
     [Company],
     [Directorat],
     [Segment],
     [Dept],
     [Division],
     [Contract Type],
     [Pro Rata Group],
     [Standard Hours/Week],
     [Esablishment Count],
     [Weeks/Year],
     [Overtime Payable?],
     [Security Level],
     [Analysis Group],
     [Cost Group]
    )
    SELECT 
    [Forms]![HR]![Text106] AS Position,
     [Forms]![New Job]![Text1] AS Title,
     #21/12/1951# AS Effective ^dd/mm/yyyy,
     IIf([Forms]![New Job]![Text4]=True,"S","F") AS Status,
     "0001" AS Company,
     IIf(Left([Forms]![HR]![Combo1],1)="X","0002","0001") AS Directorat,
     IIf(Left([Forms]![HR]![Combo1],1)="X","NA",IIf(Left([Forms]![HR]![Combo1],1)="Y","0002",IIf(Left([Forms]![HR]![Combo1],2)="AE","0005",IIf(Left([Forms]![HR]![Combo1],2)="ST","0004",IIf(AND(Left([Forms]![HR]![Combo1],1)="A",Mid([Forms]![HR]![Combo1],2,1)<>"E"),"0001","0003"))))) AS Segment,
     Left([Forms]![HR]![Combo1],3)&mid([Forms]![HR]![Combo1],2,2) AS Dept,
     [Forms]![HR]![Combo1] AS Division,
     [Forms]![New Job]![Text4] AS Contract Type,
     "261" AS Pro Rata Group,
     [Forms]![New Job]![Text6] AS Standard Hours/Week,
     "0.0000" AS Esablishment Count,
     [Forms]![New Job]![Text29] AS Weeks/Year,
     "Y" AS Overtime Payable?,
     "5" AS Security Level,
     [Forms]![New Job]![Text10] AS Analysis Group,
     [Forms]![New Job]![Text8] AS Cost Group;
    Last edited by mcinnes01; 06-28-11 at 11:20.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Rename your tables and fields, avoiding any characters that aren't letters or numbers. This will avoid a whole host of problems, possibly including the one that you're experiencing now.

    Secondly, you can next IIf statements, but I would recommend writing a VBA function to translate the value in Combo1 into the required Segment value.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jun 2011
    Posts
    5
    Sorted thanks,

    Code:
    INSERT INTO [Job Details 1] ( [Position], Title, [Effective ^dd/mm/yyyy], Status, Company, Directorat, Segment, Dept, Division, [Contract Type], [Pro Rata Group], [Standard Hours/Week], [Establishment Count], [Weeks/Year], [Overtime Payable?], [Security Level], [Analysis Group], [Cost Group] )
    SELECT [Forms]![HR]![Text106] AS [Position], [Forms]![New Job]![Text1] AS Title, #12/21/1951# AS [Effective ^dd/mm/yyyy], IIf([Forms]![New Job]![Text4]=True,"S","F") AS Status, "0001" AS Company, IIf(Left([Forms]![HR]![Combo1],1)="X","0002","0001") AS Directorat, IIf(Left([Forms]![HR]![Combo1],1)="X","NA",IIf(Left([Forms]![HR]![Combo1],1)="Y","0002",IIf(Left([Forms]![HR]![Combo1],2)="AE","0005",IIf(Left([Forms]![HR]![Combo1],2)="ST","0004",IIf(Left([Forms]![HR]![Combo1],1)="A" And Mid([Forms]![HR]![Combo1],2,1)<>"E","0001","0003"))))) AS Segment, [Forms]![New Job]![Text33] AS Dept, [Forms]![New Job]![Text37] AS Division, [Forms]![New Job]![Text4] AS [Contract Type], "261" AS [Pro Rata Group], [Forms]![New Job]![Text6] AS [Standard Hours/Week], "0.0000" AS [Establishment Count], [Forms]![New Job]![Text29] AS [Weeks/Year], "Y" AS [Overtime Payable?], "5" AS [Security Level], [Forms]![New Job]![Text10] AS [Analysis Group], [Forms]![New Job]![Text8] AS [Cost Group];

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •