Error with Append code: 1

[lars7]

Hi there,
Can anyone see why I am getting a Syntax error with this code:

Private Sub Form_Open(Cancel As Integer)
Dim strSQL2 As String
Dim strSQL As String
Set db = CurrentDb

strSQL2 = "INSERT INTO tblupdateDescriptors ( Code, JobFamily, SubJobFamily, PostDescriptor, AFCCode, Spine, [Band] ) " & _
"SELECT tbltruststaff.Code, tbltruststaff.JobFamily, tbltruststaff.SubJobFamily, tbltruststaff.PostDescriptor, tbltruststaff.AFCCode, tbltruststaff.Spine, tbltruststaff.[Band] " & _
"FROM tbltruststaff LEFT JOIN tblupdateDescriptors ON tbltruststaff.Code = tblupdateDescriptors.Code " & _
"Where (((tbltruststaff.JobFamily) <> "") And ((tblupdateDescriptors.code) Is Null)) " & _
"GROUP BY tbltruststaff.Code, tbltruststaff.JobFamily, tbltruststaff.SubJobFamily, tbltruststaff.PostDescriptor, tbltruststaff.AFCCode, tbltruststaff.Spine, tbltruststaff.[Band], tblupdateDescriptors.Code " & _
"ORDER BY tbltruststaff.JobFamily "

CurrentDb.Execute strSQL2, dbFailOnError


strSQL = "UPDATE tbltruststaff RIGHT JOIN tblupdateDescriptors ON tbltruststaff.Code = tblupdateDescriptors.Code SET tbltruststaff.JobFamily = tblupdatedescriptors.jobfamily, tbltruststaff.SubJobFamily = tblupdatedescriptors.Subjobfamily, tbltruststaff.PostDescriptor = tblupdatedescriptors.PostDescriptor, tbltruststaff.AFCCode = tblupdatedescriptors.AFCCode, tbltruststaff.Spine = tblupdatedescriptors.Spine, tbltruststaff.[Band] = tblupdatedescriptors.[Band]" & _
"WHERE (((tbltruststaff.JobFamily) Is Null))"
CurrentDb.Execute strSQL, dbFailOnError

Set db = Nothing

End Sub

The error is with the append code the update code works fine.

 

[PHV]

Why using a GROUP BY and ORDER BY clauses in an append query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

I suspect that it doesn't like [Band] in the list of fields to be inserted. Try removing the square brackets.

 

[lars7]

Hi Golom,
I took away the brackets but with the same result.

PHV,

I do need the group by as I only want to append 1 record of each code and all the fields that are attached to it and in the table I am appending from there could be hundreds of records with the same information but I removed the Order by clause.

Here is what I tried last:

strSQL2 = "INSERT INTO tblupdateDescriptors ( Code, JobFamily, SubJobFamily, PostDescriptor, AFCCode, Spine, Band ) " & _
"SELECT tbltruststaff.Code, tbltruststaff.JobFamily, tbltruststaff.SubJobFamily, tbltruststaff.PostDescriptor, tbltruststaff.AFCCode, tbltruststaff.Spine, tbltruststaff. Band " & _
"FROM tbltruststaff LEFT JOIN tblupdateDescriptors ON tbltruststaff.Code = tblupdateDescriptors.Code " & _
"Where (((tbltruststaff.JobFamily) <> "") And ((tblupdateDescriptors.code) Is Null)) " & _
"GROUP BY tbltruststaff.Code, tbltruststaff.JobFamily, tbltruststaff.SubJobFamily, tbltruststaff.PostDescriptor, tbltruststaff.AFCCode, tbltruststaff.Spine, tbltruststaff. Band, tblupdateDescriptors.Code "

CurrentDb.Execute strSQL2, dbFailOnError

 

[PHV]

And what about this ?
strSQL2 = "INSERT INTO tblupdateDescriptors ( Code, JobFamily, SubJobFamily, PostDescriptor, AFCCode, Spine, Band ) " & _
"SELECT DISTINCT A.Code, A.JobFamily, A.SubJobFamily, A.PostDescriptor, A.AFCCode, A.Spine, A. Band " & _
"FROM tbltruststaff AS A LEFT JOIN tblupdateDescriptors AS B ON A.Code = B.Code " & _
"WHERE A.JobFamily<>[tt][!]''[/!][/tt] AND B.Code Is Null"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi PHV,
I tried your code as it was, I hope that was what you ment, and got a error message "Invalid use of '.','!', or (). in expression A.Band"

strSQL2 = "INSERT INTO tblupdateDescriptors ( Code, JobFamily, SubJobFamily, PostDescriptor, AFCCode, Spine, Band ) " & _
"SELECT DISTINCT A.Code, A.JobFamily, A.SubJobFamily, A.PostDescriptor, A.AFCCode, A.Spine, A.Band " & _
"FROM tbltruststaff AS A LEFT JOIN tblupdateDescriptors AS B ON A.Code = B.Code " & _
"WHERE A.JobFamily<>'' AND B.Code Is Null"

 

[PHV]

In fact I wanted to show you where was the syntax error:
"Where (((tbltruststaff.JobFamily) <> [highlight]""[/highlight]) And ((tblupdateDescriptors.code) Is Null)) " & _

you had to use single quotes instead of double quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

In fact I wanted to show you where was the syntax error:
"Where (((tbltruststaff.JobFamily) <> [highlight]""[/highlight]) And ((tblupdateDescriptors.code) Is Null)) " & _

you had to use single quotes instead of double quotes.

And what about this ?
strSQL2 = "INSERT INTO tblupdateDescriptors ( Code, JobFamily, SubJobFamily, PostDescriptor, AFCCode, Spine, [Band] ) " & _
"SELECT DISTINCT A.Code, A.JobFamily, A.SubJobFamily, A.PostDescriptor, A.AFCCode, A.Spine, A.[Band] " & _
"FROM tbltruststaff AS A LEFT JOIN tblupdateDescriptors AS B ON A.Code = B.Code " & _
"WHERE A.JobFamily<>'' AND B.Code Is Null"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi,
That worked. Is band a reserved word? The whole code is working fine now. I think this is the answer to a post I had to abandon a couple of weeks ago but I will test a little more.
Thanks PHV you are a star.