Update Query in Access not working

pmpatane
pmpatane used Ask the Experts™
on
I had this question after viewing Update query does not work - Access 2016.

I run an update query and nothing happens.  No error, nothing.  The field, YrData, is numeric long integer and I am trying to fill in all rows with 2017.  I even tried changing it to short text and running, still nothing (See attached jpg)
Update-Query.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
There is nothing obviously wrong.  Where is the table?  is it Jet/ACE?  Is it in the FE or the BE?  If you open the table in data sheet view, can you update any columns?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are probably updating or viewing another table than what you think.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
It may be that you somehow turned off the SetWarnings indicator. In the VBE editor enter:

docmd.SetWarnings True

Now when you run the query, you should get some sort of message.

Then, you might try:

SELECT * FROM [yourTableName]

Any chance that what you think is a table is actually a query?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

John TsioumprisSoftware & Systems Engineer

Commented:
I am thinking that maybe you are having the wrong datatype in your table and you are trying to commit something that shouldn't happen...i suspect that you are using lookups in your table along with some kind of validation and mapping
So if your datatype is Number (byte) and you are trying to update with the value of 2017 it will fail because byte goes from 0 to 255...on the other hand you are seeing 2017 when you open the table...but probably there is a lookup and you are looking at the end value as there should be a table probably called Years or tblYears or something similar and it has a mapping something like
  1 ----> 2000
10 ---> 2010
17 -- > 2017
18 --> 2018

Author

Commented:
Hi all,,my apologies for the delay, I was pulled into another project. I am going to check the Set Warnings answer by Dale on Monday.  But this is just a basic table and I have gone into Create Query Design view, created an Update query on just this one field which is Number, Long Integer and I can type into the field 2017 but I obviously don't want to do that for the 1,000+ records
Commented:
Nothing worked on the database to get the Update Query to do something, anything.  I then started a blank database, imported all the objects from the offending database, and everything works fine now.  Must be some corruption in the old database.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial