Update query trouble

[bhammer]

I want to copy the CodeID value from one record to another, in the same
table, to a certain record where a part of the Doc_Number (for the record
with the CodeID) is found in the Comment field (for the record with a blank
CodeID).

Table Example:
ID CodeID Doc_Number Comment
23 16 DCS01234.jpg Yada, yada, yada.
55 0 DCS09876.jpg Blah, blah, blah. 1234.

The result should copy the 16 from record 23 into the CodeID field for
record 55. This is because the "1234" four-digit string before the file
extension was found somewhere in the Comment field of the other record.

Here's what I have tried so far:
UPDATE tblObservations SET tblObservations.CodeID = "CodeID"
WHERE (((tblObservations.Comments) Like Left(Right("Doc_Number",8),4)));

I'm on the wrong track . . .

 

[John W. Vinson]

I want to copy the CodeID value from one record to another, in the same
table, to a certain record where a part of the Doc_Number (for the record
with the CodeID) is found in the Comment field (for the record with a blank
CodeID).

Table Example:
ID CodeID Doc_Number Comment
23 16 DCS01234.jpg Yada, yada, yada.
55 0 DCS09876.jpg Blah, blah, blah. 1234.

The result should copy the 16 from record 23 into the CodeID field for
record 55. This is because the "1234" four-digit string before the file
extension was found somewhere in the Comment field of the other record.

Here's what I have tried so far:
UPDATE tblObservations SET tblObservations.CodeID = "CodeID"
WHERE (((tblObservations.Comments) Like Left(Right("Doc_Number",8),4)));

I'm on the wrong track . . .

What if several records have a match?

You could try

UPDATE yourtable AS X
SET X.CodeID =
(SELECT CodeID FROM yourtable AS Y
WHERE Y.Comment LIKE "*" & Mid(X.DocNo, 5, 4) & "*")

but I rather doubt it will be an updateable query because of the multiple
matches issue; you may be able to use

UPDATE yourtable SET codeID =
DLookUp("[CodeID]", "yourtable", "Comment LIKE *" & Mid(DocNo, 5, 4) & "*")

to get just the first matching comment.