invalid procedure call

[johnmerlino]

This query pretty much does its best to bring back a middle initial based on the contents of two tables SJ_FinalForgotten (contains the first and last name fields to compare against summary_judgment table) and summary_judgment (which has the middle initial I want to retrieve). It works when ALL records in SJ_FinalForgotten contain a comma like so:


Smith,John
Smith,Ann
Stevens,Ben

However, if there's a space in one of them:
Smith John

Then the query doesn't run and a popup displays "invalid procedure call"

This query below is what is causing the issue:

SELECT left([aname],InStr(1,[aname],",")-1) & " " 
     & right([aname],Len(aname)-InStr(1,[aname],",")) & " "
     & summary_judgment.middle_initial AS fullname 
INTO SJ_FinalForgottenWithMiddle
FROM 
  SJ_FinalForgotten INNER JOIN 
  summary_judgment ON 
    ((left(SJ_FinalForgotten.aname,InStr(1,SJ_FinalForgotten.[aname],",")-1))=summary_judgment.last_name) AND 
    ((right(SJ_FinalForgotten.aname,Len(SJ_FinalForgotten.aname)-InStr(1,SJ_FinalForgotten.[aname],","))=summary_judgment.first_name));

Thanks for response.

[ajetrumpet]

still working on this John? that's a big project!

SELECT left([aname],InStr(1,[aname],",")-1) & " " 
     & right([aname],Len(aname)-InStr(1,[aname],",")) & " "
     & summary_judgment.middle_initial AS fullname 
INTO SJ_FinalForgottenWithMiddle
FROM 
  SJ_FinalForgotten INNER JOIN 
  summary_judgment ON 
    ((left(SJ_FinalForgotten.aname,InStr(1,SJ_FinalForgotten.[aname],",")-1))=

summary_judgment.last_name) AND 


    ((right(SJ_FinalForgotten.aname,Len(SJ_FinalForgotten.aname)-

InStr(1,SJ_FinalForgotten.[aname],","))

=summary_judgment.first_name));

What you might have to do John, is put an IIF() statement around the functions that are checking for the comma in the field. I don't think there is any other way around it really, primarily because you've already done so much work getting this far. for instance, this part of the query:

left([aname],InStr(1,[aname],",")-1) & " "

would have to be changed to something like this:

left([aname],

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

InStr(1,[aname]," ")-1),  

InStr(1,[aname],",")-1)) & " "

[johnmerlino]

You mean like this (which is giving a missing operator syntax error)?

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 
((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);

Thanks for response.

[ajetrumpet]

It's probably right John. Remember that my solution was not the only one, and it has to be expanded upon more than what I provided, because it doesn't catch all the possibilities. I would suggest maybe labeling your sql in another program, so you can sort it out and debug it faster.

[johnmerlino]

I think it just means there's a syntax error at "AS fullname INTO ":

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 
((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);

There may be more errors. but I'm trying to address one at a time. Right now I'm not sure why i get syntax error at As clause.

[johnmerlino]

Alright I got it working. It was missing paranethesis