Update Query not working

Question

Update Query not working

puforee (TechnicalUser)

(OP)

6 Feb 19 18:13

I have a table with a field named "Complete". When I update data in this table I also want to check to see if work has been completed. If it is completed I don't want the user to see the completed items. The users work on items that are not complete due to errors. Not all items have errors so many are not used. Once those items are completed I want to remove them from user visibility to reduce a selection list that the users use.

I use a query to find the Items that are complete from an input table. I then created an update query on the (IPSS Classes) table linked to the query Update Complete Field. In View mode this shows the correct field to modify based on the item number. View runs fine but Run returns:

A Warning indicating changes cannot be undone. "expected"
Operation must use an updateable query. "not expected"

Here is my query code:

CODE -->

UPDATE Breakout_IPSS_End INNER JOIN [IPSS Classes] ON Breakout_IPSS_End.[Related Entity ID] = [IPSS Classes].IPSSClassID SET [IPSS Classes].Complete = Yes;

IPSS Classes.Complete is a check box in the table

I need the first query to find the items that are completed in the input data. These are the only items that should be updated in the IPSS Classes table complete field.

The completed value is from a different table than the IPSS Classes table. A little confusing but .... Suggestions?

Thanks, John

Andrzejek (Programmer) · Answer 1 · 2019-02-06 18:29:31

Is this what you are trying to do... ponder

UPDATE [IPSS Classes]
SET Complete = 'Yes'
WHERE IPSSClassID IN (
SELECT [Related Entity ID] FROM Breakout_IPSS_End)

---- Andy

There is a great need for a sarcasm font.

puforee (TechnicalUser) · Answer 2 · 2019-02-06 18:41:30

Yes. If that the proper format? If yes, why does it come out wrong in query builder.

puforee (TechnicalUser) · Answer 3 · 2019-02-06 18:49:55

I placed your code in my query and then looked at Design View. So, the first query is not even needed except as criteria for the IPSSClassID field. Interesting. I guess I still have a lot to learn about query's in MS access.

I will now run it.

It ran but I have to do some more work...I have a type conversion error.

I changed "Yes" to Yes and it worked. The field being updated is a check box not a text field.

Great and quick answer. I will log this on in my brain trust book for future use.

A Star to you Andy.

puforee (TechnicalUser) · Answer 4 · 2019-02-06 18:52:26

First line above - I looked at "view" mode not "design view"

Andrzejek (Programmer) · Answer 5 · 2019-02-06 19:31:58

When I looked at your SQL:

UPDATE Breakout_IPSS_End
INNER JOIN [IPSS Classes]
ON Breakout_IPSS_End.[Related Entity ID] = [IPSS Classes].IPSSClassID
SET [IPSS Classes].Complete = Yes;

I saw simple something like, let's say:

UPDATE Breakout_IPSS_End
SET [IPSS Classes].Complete = Yes;

So you were trying to UPDATE something in a Breakout_IPSS_End table, but the field Complete is in [IPSS Classes] table ponder

That's why I switched around and gave you my version....

I'm glad it worked...

Oh, and I believe the checkbox in Access takes the values of 0 for False and -1 for True, but you would have to try it to make sure.

---- Andy

There is a great need for a sarcasm font.

puforee (TechnicalUser) · Answer 6 · 2019-02-06 20:28:10

Thanks. And the check box will take either, Yes/No or -1/0. Probably to be cleaner I should set it to -1 but I chose Yes. Thanks again.

Come Join Us!

Posting Guidelines

Jobs

Update Query not working