Syntax Error(missing operator) in query expression - MS-Access

0
votes

This doesn't produce syntax errors but it gives wrong arguments error:

SELECT left(
  [aname],
 IIF(instr([aname], " ") = 0 AND instr([aname], ",") = 0,
   Len(FinalForgotten.aname),
  IIF(instr([aname], ",") = 0,
    InStr(1,[aname]," ")-1,
    InStr(1,[aname],",")-1)))
&  ","  &

right(
   [aname],
 IIF(instr([aname], " " = 0 AND instr([aname], ",") = 0,
   Len(FinalForgotten.aname),
    IIF(instr([aname], ",") = 0,
      Len(aname)-InStr(1,[aname]," "),
      Len(aname)-InStr(1,[aname],",")))))
 &  " " &

defense_final.middle_initial AS fullname INTO FinalForgottenWithMiddle
FROM FinalForgotten INNER JOIN defense_final ON (right(FinalForgotten.aname,
 IIF(instr([aname], ",") = 0,
 Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname]," "),
 Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname],",")
  )
 )=defense_final.first_name) AND (left(FinalForgotten.aname,
IIF(instr([aname], ",") = 0,
  InStr(1,FinalForgotten.[aname]," ")-1,
  InStr(1,FinalForgotten.[aname],",")-1
    )
)=defense_final.last_name);

So adding the extra arguments I now get syntax error "Missing ), ], or Item in query expression" :

   SELECT left(
  [aname],
 IIF(instr([aname], " ") = 0 AND instr([aname], ",") = 0,
   Len(FinalForgotten.aname),
  IIF(instr([aname], ",") = 0,
    InStr(1,[aname]," ")-1,
    InStr(1,[aname],",")-1)))
&  ","  &

right(
   [aname],
 IIF(instr([aname], " ") = 0 AND instr([aname], ",") = 0,
   Len(FinalForgotten.aname),
    IIF(instr([aname], ",") = 0,
      Len(aname)-InStr(1,[aname]," "),
      Len(aname)-InStr(1,[aname],","))))
 &  " " &

defense_final.middle_initial AS fullname INTO FinalForgottenWithMiddle
FROM FinalForgotten INNER JOIN defense_final ON 

left(FinalForgotten.aname,
   IIF(instr([aname], " ") = 0 AND instr([aname], ",") = 0,
      Len(FinalForgotten.aname),
      IIF(instr([aname], ",") = 0,
        InStr(1,FinalForgotten.[aname]," ")-1,
        InStr(1,FinalForgotten.[aname],",")-1)))

 =defense_final.last_name AND 

 right(FinalForgotten.aname,
      IIF(instr([aname], " ") = 0 AND instr([aname], ",") = 0,
      Len(FinalForgotten.aname),
       IIF(instr([aname], ",") = 0,
        Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname]," "),
        Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname],","))))

=defense_final.first_name;

Any workaround?

Thanks for response.

4
ms-accesssyntaxoperator-keyword
may I suggest that you give your table a SHORT (1 letter) alias, and update your question ? This would make it more readable I think. – Patrick Honorez
you mention "wrong arguments error". Can you specify the error ? Usually that comes when a field is misspelled. – Patrick Honorez
@iDevlop It says "wrong number of arguments used with function in query expression 'left(..." – JohnMerlino

4 Answers

0
votes

line 10

right(

has no corresponding right parenthesis

0
votes

SELECT left( [aname], So [aname] is a text field.

 IIF(instr([aname], " ") = 0 AND instr([aname], ",") = 0,

If this condition is true you want the length of the Left String to be:

 FinalForgotten.aname,

Which is not an integer. You want: 

SELECT left(
  [aname],
 IIF(instr([aname], " ") = 0 AND instr([aname], ",") = 0,
   Len(FinalForgotten.aname),
0
votes
 IIF(instr([aname], " " = 0 AND instr([aname], ",") = 0,

on line 12 should be

 IIF(instr([aname], " ") = 0 AND instr([aname], ",") = 0,
0
votes

It would be easier to put that logic into VBA functions so your query looks like this

SELECT GetFullNameWithMiddle([aname]) AS fullname
INTO FinalForgottenWithMiddle
FROM FinalForgotten INNER JOIN defense_final
    ON GetLastName([aname])=defense_final.last_name
    AND GetFirstName([aname])=defense_final.first_name

Since you say you don't want to use VBA, try this instead. I enclosed every

InStr(x, y) = 0

in parentheses, so 

IIf(InStr(x, y) = 0 AND InStr(p, q) = 0, g, h)

becomes

IIf((InStr(x, y) = 0) AND (InStr(p, q) = 0), g, h)

Making that change allowed me to switch into the query design grid, which means that the SQL parser liked it:

SELECT left(
    [aname],
    IIF((instr([aname], " ") = 0) AND (instr([aname], ",") = 0),
    Len(FinalForgotten.aname),
    IIF((instr([aname], ",") = 0),
    InStr(1,[aname]," ")-1,
    InStr(1,[aname],",")-1)))

    &  ","  &

right(
    [aname],
    IIF((instr([aname], " ") = 0) AND (instr([aname], ",") = 0),
    Len(FinalForgotten.aname),
    IIF((instr([aname], ",") = 0),
      Len(aname)-InStr(1,[aname]," "),
      Len(aname)-InStr(1,[aname],","))))

    &  " " &

defense_final.middle_initial AS fullname INTO FinalForgottenWithMiddle
FROM FinalForgotten INNER JOIN defense_final ON 

left(FinalForgotten.aname,
    IIF((instr([aname], " ") = 0) AND (instr([aname], ",") = 0),
    Len(FinalForgotten.aname),
    IIF((instr([aname], ",") = 0),
    InStr(1,FinalForgotten.[aname]," ")-1,
    InStr(1,FinalForgotten.[aname],",")-1)))

=defense_final.last_name AND 

right(FinalForgotten.aname,
    IIF((instr([aname], " ") = 0) AND (instr([aname], ",") = 0),
    Len(FinalForgotten.aname),
    IIF((instr([aname], ",") = 0),
    Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname]," "),
    Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname],","))))

=defense_final.first_name;