Update Matched Records

[mikerea90]

Right now I have an update query that does not seem to be working.

The update query is updated information based on two tables. The query has two fields it looks at: StockNumber and Description.

So where [Table1]![StockNumber] = [Table2]![Stocknumber] and [Table1]![Description] = [Table2]![Description], I want this to update [Table1]![Status] to "Sold."

This does not work, I get a prompt asking me to 'Enter Parameter Value' for these two fields. Any advice would be appreciated! Thanks

 

[Galaxiom]

The tables should be joined on those fields rather than using those expression in the Where clause.

Post the actual SQL.

 

[mikerea90]

This is what I have right now:

UPDATE Table1 SET Table1.[Group] = "A"
WHERE (((Table1.StockNumber)=[Table2]![StockNumber]) AND ((Table1.Description)=[Table2]![Description]));

 

[mikerea90]

I have changed it to:

SELECT [StockNumber], [Description], [Group], Table2.StockNumber, Table2.Description
FROM Table1 INNER JOIN Table1 ON (Table2.StockNumber=Table1.StockNumber And Table2.Description=Table1.Description)
ORDER BY [Description];

This does not work. I get an error on Join.

 

[MSAccessRookie]

I have changed it to:

SELECT [StockNumber], [Description], [Group], Table2.StockNumber, Table2.Description
FROM Table1 INNER JOIN Table2 ON (Table2.StockNumber=Table1.StockNumber And Table2.Description=Table1.Description)
ORDER BY [Description];

This does not work. I get an error on Join.

Try the correction marked in RED. You can also switch the order of the two tables.

 

[mikerea90]

Works. Thanks!