Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL syntax problem (2000 (all updates))

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL syntax problem (2000 (all updates))

    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 = ".

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL syntax problem (2000 (all updates))

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL syntax problem (2000 (all updates))

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

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL syntax problem (2000 (all updates))

    Hans
    That's got it!

    I have to do some reading about the SET statement.

    Thanks again!

    Tom

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •