SQL syntax problem (2000 (all updates))

[THWatson]

In my SQL I want to be able to select subform records where AssignedTo = either "A" or "C" and move them to a newly created record's subform.

I tried...
' Update query to move "C" and "A" records to new record's subform
strSQL = "UPDATE tblEnvelopeNumbers SET UniqueID = " & lngNewUniqueID & _
" WHERE AssignedTo = 'A' Or 'C' And UniqueID = " & lngOldUniqueID
DoCmd.RunSQL strSQL

That moves all subform records, whether "A" or "B" or "C"

So I changed the SQL to...
' Update query to move "C" and "A" records to new record's subform
strSQL = "UPDATE tblEnvelopeNumbers SET UniqueID = " & lngNewUniqueID & _
" WHERE AssignedTo <> 'B' And UniqueID = " & lngOldUniqueID
DoCmd.RunSQL strSQL

That moves the appropriate AssignedTo records, however this next piece of code does not change the "C" records to "A", only changes "B" records.
' Set AssignedTo in both old and new records to "A"
With rst
.Requery
If .RecordCount = 0 Then GoTo UpdateForm
.MoveFirst
Do Until .EOF
.Edit
!AssignedTo = "A"
.Update
.MoveNext
Loop
End With

Any suggestions as to how I can fix it?

Thanks.
Tom

[HansV]

In the first SQL in your post, replace the line

" WHERE AssignedTo = 'A' Or 'C' And UniqueID = " & lngOldUniqueID

with

" WHERE (AssignedTo = 'A' Or AssignedTo = 'C') And UniqueID = " & lngOldUniqueID

Note the use of parentheses ( ) and the repetition of "AssignedTo = ".

[THWatson]

Hans
No go. Actually, I think that is one of the variations I tried.

It does move the subform records...but doesn't change the "C" designation to "A"

Tom

[HansV]

strSQL = "UPDATE tblEnvelopeNumbers SET AssignedTo = 'A', UniqueID = " & lngNewUniqueID & _
" WHERE (AssignedTo = 'A' Or AssignedTo = 'C') And UniqueID = " & lngOldUniqueID

[THWatson]

Hans
That's got it!

I have to do some reading about the SET statement.

Thanks again!

Tom