UPDATE query not working

[BrockWade]

Can anyone see a problem in this query where I'm trying to update a field called tmpBankDebits.Matched to "Y" IF there is a match between
tmpBankDebits.ConcatenateBankDebits AND tmpChangeOrdersWithManual.ConcatenateBank?



UPDATE tmpBankDebits INNER JOIN tmpChangeOrdersWithManual
ON tmpBankDebits.ConcatenateBankDebits =
tmpChangeOrdersWithManual.ConcatenateBank SET tmpBankDebits.Matched = "Y";

[June7]

Is the field a Yes/No or text data type? If Yes/No, don't put the value with quote marks and use Yes or True or -1.

Why do you even need to save this? Saving calculated data is not usually recommended. Whether or not there is a match can be calculated when needed.

[BrockWade]

tmpBankDebits.Matched is a text field, so I changed it to Yes with no quotes. Still did not work.

Long story but I can find no other way around tagging these records for further processing than the proposed query.

The end result needs to be that of roughly 850 records in tmpBankDebits, 160 need to be tagged Yes if tmpBankDebits.ConcatenateBankDebits =
tmpChangeOrdersWithManual.ConcatenateBank... currrently it is tagging (returning) over 1,000 records.

[June7]

Any UPDATE query that can successfully edit the field can just as easily be a simple SELECT query. The same filter criteria has to work for both.

Calculate a field in the query and apply filter criteria to that field.

IsMatch: IIf(tmpBankDebits.ConcatenateBankDebits = tmpChangeOrdersWithManual.ConcatenateBank, "Yes", "No")

Why the query tags more records is another issue. I don't understand the data relationships to address that.

[BrockWade]

This doesn't seem to work due to syntax:

UPDATE tmpBankDebits.Matched
Matched: IIf(tmpBankDebits.ConcatenateBankDebits = tmpChangeOrdersWithManual.ConcatenateBank, "Yes", "No");

[June7]

No, that won't. I was offering a suggestion to construct a field in a SELECT query, not an UPDATE, then apply filter criteria to that constructed field, not edit the field. Cannot do an edit on a calculated field.