Missing a parenthesis somewhere in query

**johnmerlino** · 12-19-2010, 02:20 PM

Hey all,

I have this query:

Code:

SELECT left(
      [aname],
   IIF((instr([aname], " ") = 0 AND ((instr([aname], ",") = 0,
      [aname],
      IIF(instr([aname], ",") = 0,
        InStr(1,[aname]," ")-1,
        InStr(1,[aname],",")-1
        )
      )
   )
    &  ","  &
    right(
       [aname],
   IIF((instr([aname], " " = 0 AND ((instr([aname], ",") = 0,
       "",
        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,
      [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,
      [aname],
       IIF(instr([aname], ",") = 0,
        Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname]," "),
        Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname],",")
      )
    )
   )=defense_final.first_name);

I am missing a paranethesis somewhere and I get error Syntax Error(missing operator) in Query Expression and it highlights the word AS in AS fullname. I don't want to do significant change to this query like adding functions. I just want to figure out where the missing parenthesis is.

Thanks for response.

**ajetrumpet** · 12-19-2010, 02:48 PM

TRY THIS:

Code:

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

    right(
       [aname],
   IIF(instr([aname], " " = 0 AND instr([aname], ",") = 0,
       "",
        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,
      [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,
      [aname],
       IIF(instr([aname], ",") = 0,
        Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname]," "),
        Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname],","))))

=defense_final.first_name;

are you sure the error is talking about a syntax error tho?

**johnmerlino** · 12-19-2010, 02:56 PM

That doesn't seem to work either. I assumed it was a parenthesis error. Because I had this before and it worked:

Code:

SELECT left(
      [aname], 
      IIF(instr([aname], ",") = 0,
        InStr(1,[aname]," ")-1,
        InStr(1,[aname],",")-1
        ) 
      )
    &  ","  & 
    right(
       [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,
      InStr(1,FinalForgotten.[aname]," ")-1,
      InStr(1,FinalForgotten.[aname],",")-1
        )
    )=defense_final.last_name) AND (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);

So all I am doing now is adding layer:

Code:

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

Because some of the names will not have a space or comma, so I need to account for that with this extra layer.

Thanks for response.

**ajetrumpet** · 12-19-2010, 04:05 PM

well John, this doesn't make sense:

Code:

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

because there are extra and unnecessary parenthesis (3 of them) in:

Code:

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

that don't have a closing ones in the right place.

**johnmerlino** · 12-19-2010, 04:46 PM

Yeah but even when removing the extra paranthesis there, it still gives me same error.

**ajetrumpet** · 12-19-2010, 04:59 PM

Originally Posted by johnmerlino

Yeah but even when removing the extra paranthesis there, it still gives me same error.

is it too complex I wonder?

and even if you remove just those John, the closers that you originally had in it somewhere else in the statement would still error.

But that's irrelevant, I already gave you a correct syntax version of your statement.

I also wonder if the relative references you have aren't causing problems. e.g. - change:

Code:

[aname]

to the full:

Code:

TABLE.aname

would that be an issue?

also, can you use 'AND' in a join statement? I took my VBA database and joined 3 tables and tried the same and I got:

Code:

SELECT left(library.lfile, 5) & "-" & coclass.ctype & "-" & 

right(method.mname, 3) INTO Table1

FROM (coclass INNER JOIN library ON coclass.lid = library.lid) INNER JOIN method 

ON coclass.cid = method.cid;

why not try using the builder the fields as they are, let it auto join for you, then add your LEFT and RIGHT functions back in?

mine above nested the joins twice. would that matter I wonder?

**johnmerlino** · 12-19-2010, 08:30 PM

Thanks for reply. I think the RIGHT was missing a right parenthesis but now that I add that parenthesis:

Code:

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

right(
   [aname],
 IIF(instr([aname], " " = 0 AND instr([aname], ",") = 0,
   "",
    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,
  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,
  [aname],
   IIF(instr([aname], ",") = 0,
    Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname]," "),
    Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname],","))))=defense_final.first_name;

I get an error:
"Missing ), ], or Item in query expression" and it places the cursor on the first AND.

Thanks for response.

**weekend00** · 12-20-2010, 09:14 AM

John, if I understand correctly, you are selecting "firstname,lastname middle" from the matching list.

I think there could be some easier way to achieve this.

Code:

SELECT replace(aname," ", ",") &  " " & defense_final.middle_initial AS fullname INTO FinalForgottenWithMiddle
FROM FinalForgotten INNER JOIN defense_final ON 
left(aname, len(first_name))=first_name and 
left(aname,len(first_name)+1)<(first_name & ".") AND 
right(aname,len(last_name)=last_name and 
right(aname,len(last_name)+1)<(last_name & ".")

some conditions must apply to run this query successfully.
================================================== ======
In you query, you miss len() around aname:

Code:

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

right(
   [aname],
 IIF(instr([aname], " " = 0 AND instr([aname], ",") = 0,
   "",
    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,
  FinalForgotten.aname,    should be len(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,
  [aname],  should be len(aname)
   IIF(instr([aname], ",") = 0,
    Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname]," "),
    Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname],","))))=defense_final.first_name;

**johnmerlino** · 12-20-2010, 10:36 AM

Thanks for response but still get an error "Missing ), ], or Item in query expression":

Code:

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,
   "",
    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;

Also, while your other query looks nicer, you specify a first_name field that doesn't exist. So how can I compare a first_name field if aname contains both first and last? That's why I had to break it up in above query.

**weekend00** · 12-20-2010, 12:02 PM

Also, while your other query looks nicer, you specify a first_name field that doesn't exist. So how can I compare a first_name field if aname contains both first and last? That's why I had to break it up in above query.

Last_name is from table defense_final.

I should exchange the places of last_name and first_name.

**johnmerlino** · 12-20-2010, 07:14 PM

Thanks for responses. Once again, it was ultimately missing paranethesis that caused the compiler to throw exception. I think it's time to learn so VBA though. Wish it was more like PHP and JavaScript though, at least with function declarations and variable assignment.

**weekend00** · 12-21-2010, 08:26 AM

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,
"", should be 0 or len("")
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

**weekend00** · 12-21-2010, 08:34 AM

I tested this query and it worked:
SELECT replace(aname," ", ",") & " " & middle_initial AS fullname
FROM FinalForgotten as a INNER JOIN defense_final as b ON
left(a.aname, len(b.first_name))=b.first_name and
left(a.aname,len(b.first_name)+1)<(b.first_name & ".") AND
right(a.aname,len(b.last_name))=b.last_name and
right(a.aname,len(b.last_name)+1)<(b.last_name & ".")

Missing a parenthesis somewhere in query

Thread Tools

Missing a parenthesis somewhere in query

Similar Threads

ok what am i missing here ?

Missing file

How to find missing date query

Missing Counts that = 0 in query results

Syntax error (missing operator) in query expression 'NEXTVAL for [ IDV_CLIENT_SEQ ]'.

Tags for this Thread

Posting Permissions