Duplicate output Destination in append qry

[option]

Hey guys,

I've been searching the forums and I can't seem to make heads or tails out of this error. I looked through my SQL statement to find a duplicate field and no dice on that. Heck of it is, I made the append query like normal and it works. I copied the SQL and put it into a module, and it doesn't work! Can anyone spot whats wrong here? I'm pretty new to messing with SQL:

strSQL = "INSERT INTO tblEmpInfo (AccountNumber, AgentID, Name, HireDate, TermDate, ActiveFlag, ProcCode, Dept, DeptDesc)" & _
         "SELECT tblInput.AccountNum, Right(EMP_ID,5) AS AgentID, EWFM_EmpInfo.EMP_SORT_NAME, EWFM_EmpInfo.EMP_EFF_HIRE_DATE,EWFM_EmpInfo.EMP_TERM_DATE, EWFM_EmpInfo.EMP_ACTIVE_FLAG, EWFM_EmpInfo.EMP_EXTRA_2, EWFM_EmpInfo.EMP_CLASS_2, EWFM_EmpInfo.EMP_CLASS_2_DESCR," & _
         "*FROM tblInput INNER JOIN EWFM_EmpInfo ON EWFM_EmpInfo.EMP_SORT_NAME = tblInput.Name"

 

[pbaldy]

Try a space instead of the ",*" in front of "FROM". You may also need one in front of "SELECT".

 

[MSAccessRookie]

Hey guys,

I've been searching the forums and I can't seem to make heads or tails out of this error. I looked through my SQL statement to find a duplicate field and no dice on that. Heck of it is, I made the append query like normal and it works. I copied the SQL and put it into a module, and it doesn't work! Can anyone spot whats wrong here? I'm pretty new to messing with SQL:

strSQL = "INSERT INTO tblEmpInfo (AccountNumber, AgentID, Name, HireDate, TermDate, ActiveFlag, ProcCode, Dept, DeptDesc)" & _
         "SELECT tblInput.AccountNum, Right(EMP_ID,5) AS AgentID, EWFM_EmpInfo.EMP_SORT_NAME, EWFM_EmpInfo.EMP_EFF_HIRE_DATE,EWFM_EmpInfo.EMP_TERM_DATE, EWFM_EmpInfo.EMP_ACTIVE_FLAG, EWFM_EmpInfo.EMP_EXTRA_2, EWFM_EmpInfo.EMP_CLASS_2, EWFM_EmpInfo.EMP_CLASS_2_DESCR," & _
         "[SIZE=4][COLOR=red][B]*[/B][/COLOR][/SIZE]FROM tblInput INNER JOIN EWFM_EmpInfo ON EWFM_EmpInfo.EMP_SORT_NAME = tblInput.Name"

Can you explain the purpose of the *? The rest of the code looks OK, but I have never seen a * used in that context.

COMMENT: I noticed that pbaldy had replied about the same time that I did. He is correct (as long as the * is an error in typinjg. You are missing spaces in from of both SELECT and FROM. You also have an extra Comma after EWFM_EmpInfo.EMP_CLASS_2_DESCR. Fixing these should make it work.

 

[option]

Looks like that was something I forgot to take out! Maybe that's whats throwing this off...? hahaha

Edit: I tried your suggestion, and my new error is "syntax error in INSERT INTO statement". I tried this as well:

INSERT INTO tblEmpInfo ( AccountNumber, AgentID, Name, HireDate, TermDate, ActiveFlag, ProcCode, Dept, DeptDesc )
SELECT tblInput.AccountNum, Right([EMP_ID],5) AS AgentID, EWFM_EmpInfo.EMP_SORT_NAME, EWFM_EmpInfo.EMP_EFF_HIRE_DATE, EWFM_EmpInfo.EMP_TERM_DATE, EWFM_EmpInfo.EMP_ACTIVE_FLAG, EWFM_EmpInfo.EMP_EXTRA_2, EWFM_EmpInfo.EMP_CLASS_2, EWFM_EmpInfo.EMP_CLASS_2_DESCR
FROM tblInput INNER JOIN EWFM_EmpInfo ON tblInput.Name = EWFM_EmpInfo.EMP_SORT_NAME;

and still the same error.

 

[boblarson]

There's a good chance that you are having issues with the column named "Name" as that is an Access reserved word. You should change that to make life better for yourself. In the meantime, anywhere you want to refer to it as a field name it will need to be encapsulated in square brackets - [Name]

 

[option]

So in other words:

INSERT INTO tblEmpInfo ( AccountNumber, AgentID, [Name], HireDate, TermDate, ActiveFlag, ProcCode, Dept, DeptDesc )
SELECT tblInput.AccountNum, Right([EMP_ID],5) AS AgentID, EWFM_EmpInfo.EMP_SORT_NAME, EWFM_EmpInfo.EMP_EFF_HIRE_DATE, EWFM_EmpInfo.EMP_TERM_DATE, EWFM_EmpInfo.EMP_ACTIVE_FLAG, EWFM_EmpInfo.EMP_EXTRA_2, EWFM_EmpInfo.EMP_CLASS_2, EWFM_EmpInfo.EMP_CLASS_2_DESCR
FROM tblInput INNER JOIN EWFM_EmpInfo ON tblInput.[Name] = EWFM_EmpInfo.EMP_SORT_NAME;

or did I miss something?