Update query not working

[RexJacobus]

I have a table full of item numbers. I try to condense the table using the following query SQL

UPDATE tbl2009Sales SET tbl2009Sales.Item = "PN"
WHERE (((tbl2009Sales.Item) Like "PN*"));

This is the SQL generated by the design wizard but it looks like good code to me. It should, in theory take all PN150, PNJ150, PN303, and PNN303 and have the field simply say PN.

But when I run the query nothing happens. No msg, no update, nothing.

Any ideas what is going wrong?

thanks,

Rex

 

[dhookom]

Is Item a lookup field? How exactly are you running the query?

What do you see with a query like:

SELECT tbl2009Sales.*
FROM tbl2009Sales
WHERE tbl2009Sales.Item Like "PN*";

Duane
Hook'D on Access
MS Access MVP

 

[hefly]

I have a similar problem. My relationships are defined with cascade update and delete. I have primary keys in both tables and a matching foreign key in table two.


SELECT tbl_One.RowNo, tbl_Ones.InstrNo, tbl_two.ANo, tbl_two.RowNo, tbl_One.Description
FROM tbl_One LEFT JOIN tbl_two ON tbl_One.RowNo = tbl_two.RowNo
WHERE (((tbl_two.ANo) Is Null) AND ((tbl_one.Description) Like "*100*"));

I can manually update a record (ANo field) and all is well. When I run the update query, no error message appears. The queery runs, but no changes are made.

It is a puzzlement.

Thanks.

Hefly

 

[hefly]

Ooops. I didn't enclose the update query:

UPDATE tbl_One LEFT JOIN tbl_tow ON tbl_One.RowNo = tbl_two.RowNo SET tbl_two.ANo = 579
WHERE (((tbl_two.ANo) Is Null) AND ((tbl_one.Description) Like "*579*"));

No error message, and no Update occurs.

 

[PHV]

What about replacing LEFT JOIN with INNER JOIN (as you obviously can't update non-existent rows)?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hefly]

Correction:

UPDATE tbl_One LEFT JOIN tbl_two ON tbl_One.RowNo = tbl_two.RowNo SET tbl_two.ANo = 579WHERE (((tbl_two.ANo) Is Null) AND ((tbl_one.Description) Like "*579*"));

 

[RexJacobus]

Thanks, Duane.

When I create a query and use

SELECT tbl2009Sales.*
FROM tbl2009Sales
WHERE tbl2009Sales.Item Like "PN*";

It selects exactly the fields I want. When I turn my query into a select query it selects exactly the fields I want. When I created a query using your code and changed the query to an update query nothing happens.

The table has no relationships (it has just been imported from XL) and Item is a standard Text field.

Rex

 

[dhookom]

What about my question "How exactly are you running the query"?

Just changing the query to an update query and viewing its datasheet view will do nothing. You must execute/run the query.

Duane
Hook'D on Access
MS Access MVP

 

[RexJacobus]

I tried clicking the run exclamation point from within the query and I also tried closing the query and then right clicking on it and choosing Open.

In the end I gave up, exported into XL, sorted by item code, then cut and pasted the 12 different code prefixes. Then I imported the table back into Access.

Seemed really stupid but I got the report done.

 

[dhookom]

The table wasn't a linked worksheet from Excel was it?

Duane
Hook'D on Access
MS Access MVP

 

[RexJacobus]

No it was imported not linked.

 

[dhookom]

Can you even type/edit field values in the table?

Duane
Hook'D on Access
MS Access MVP