Help with Update query SQL

[Alex]

I'm trying to update the AllNewParts tbl with fields from the ExcelARefParts
tbl (I know I shouldn't be duplicating fields, but it's necessary in this
db.) I would greatly appreciate it if someone please help me with my syntax?
Thanks.

CurrentDb.Execute "UPDATE AllNewParts SET AllNewParts.RefPartNHL =
ExcelARefParts.AS Part#" & _
"WHERE AllNewParts.RefPart = ExcelARefParts.Part#" & _
"AND ExcelARefParts.Part# <> ExcelARfParts.AS Part#" & _
"AND ExcelARefParts.Variable > 0;"

 

[Jeff L]

"UPDATE AllNewParts Inner Join ExcelARefParts On AllNewParts.RefPart =
ExcelARefParts.Part#" & _
"SET AllNewParts.RefPartNHL = ExcelARefParts.[AS Part#]" & _
"WHERE ExcelARefParts.Part# <> ExcelARefParts.[AS Part#]" & _
"AND ExcelARefParts.Variable > 0;"

 

[John Vinson]

I'm trying to update the AllNewParts tbl with fields from the ExcelARefParts
tbl (I know I shouldn't be duplicating fields, but it's necessary in this
db.) I would greatly appreciate it if someone please help me with my syntax?
Thanks.

CurrentDb.Execute "UPDATE AllNewParts SET AllNewParts.RefPartNHL =
ExcelARefParts.AS Part#" & _
"WHERE AllNewParts.RefPart = ExcelARefParts.Part#" & _
"AND ExcelARefParts.Part# <> ExcelARfParts.AS Part#" & _
"AND ExcelARefParts.Variable > 0;"

Whenever you have special characters such as blanks - or, probably, #
as well - in a fieldname you must delimit the fieldname with [square
brackets].

Try

CurrentDb.Execute "UPDATE AllNewParts
SET AllNewParts.RefPartNHL = ExcelARefParts.[AS Part#]" & _
"WHERE AllNewParts.RefPart = ExcelARefParts.[Part#]" & _
"AND ExcelARefParts.[Part#] <> ExcelARfParts.[AS Part#]" & _
"AND ExcelARefParts.Variable > 0;"


John W. Vinson[MVP]