×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Update Query not working

Update Query not working

Update Query not working

(OP)
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

RE: Update Query not working

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.

RE: Update Query not working

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

RE: Update Query not working

(OP)
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.

RE: Update Query not working

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

RE: Update Query not working

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... smile

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.

RE: Update Query not working

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close