Results 1 to 11 of 11
  1. #1
    WesHarding is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    60

    Update query not updating---thoughts?

    Good morning community,

    Today I have done my first attempt at an update query. My main goal it to populate all null cells in my table as "na" (of course except for my primary key field). I looked up some methods on doing this and it seemed pretty straight forward. I included the table I wanted, selected the fields I wanted to apply this condition to, and then entered the conditions with an update to: "na".
    SQL:
    UPDATE tblWHO SET tblWHO.Municipality_WHO = "na", tblWHO.Phone1C1 = "na", tblWHO.Phone2C1 = "na", tblWHO.FarmName = "na", tblWHO.FarmCivicAddress = "na", tblWHO.FarmCommunity = "na", tblWHO.FarmPostalCode = "na", tblWHO.Province = "na", tblWHO.Email = "na", tblWHO.Website = "na", tblWHO.Fax = "na", tblWHO.Facebook = "na", tblWHO.DateofEntry = "na", tblWHO.Notes = "na", tblWHO.RR = "na"
    WHERE (((tblWHO.Municipality_WHO)="isnull") AND ((tblWHO.Phone1C1)="isnull") AND ((tblWHO.Phone2C1)="isnull") AND ((tblWHO.FarmName)="isnull") AND ((tblWHO.FarmCivicAddress)="isnull") AND ((tblWHO.FarmCommunity)="isnull") AND ((tblWHO.FarmPostalCode)="isnull") AND ((tblWHO.Province)="isnull"));

    When I run the query, it is telling me that it is updating (0) rows. However, I know that there are rows that have null values. Any thoughts?

    to be noted: These tables were imported from Excel. I'm not sure if the null values have not be recognized as such since being imported from Excel.

    Thank you in advance!



    Wesley

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Leave "" off of isnull


    Sent from my iPhone using Tapatalk

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    All those ANDs mean all or nothing. Are all the fields null?
    Change all the = null to 'is null' (wo the quote) in the WHERE clause.
    Putting na in a date field or numeric field would be a problem.
    Last edited by davegri; 03-16-2017 at 09:00 AM. Reason: edit

  4. #4
    WesHarding is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    60
    I believe there is something more fundamentally wrong with what I am doing. I just attempted to update one field at a time, and still am encountering the same issue.

    Could there be an alternate criteria I should try to better state that I am looking for empty cells?

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ?? Not sure of why you would have an empty record to update, but we don't know anything about your business.

    Here is your SQL formatted for readability
    Code:
    UPDATE tblWHO
    SET tblWHO.Municipality_WHO = "na"
    	,tblWHO.Phone1C1 = "na"
    	,tblWHO.Phone2C1 = "na"
    	,tblWHO.FarmName = "na"
    	,tblWHO.FarmCivicAddress = "na"
    	,tblWHO.FarmCommunity = "na"
    	,tblWHO.FarmPostalCode = "na"
    	,tblWHO.Province = "na"
    	,tblWHO.Email = "na"
    	,tblWHO.Website = "na"
    	,tblWHO.Fax = "na"
    	,tblWHO.Facebook = "na"
    	,tblWHO.DateofEntry = "na"
    	,tblWHO.Notes = "na"
    	,tblWHO.RR = "na"
    WHERE (
    		((tblWHO.Municipality_WHO) = "isnull")
    		AND ((tblWHO.Phone1C1) = "isnull")
    		AND ((tblWHO.Phone2C1) = "isnull")
    		AND ((tblWHO.FarmName) = "isnull")
    		AND ((tblWHO.FarmCivicAddress) = "isnull")
    		AND ((tblWHO.FarmCommunity) = "isnull")
    		AND ((tblWHO.FarmPostalCode) = "isnull")
    		AND ((tblWHO.Province) = "isnull")
    		);
    "IsNull" is comparing the field value to a string --not what you intended I'm sure.

    Are you trying to insert new records into a Table OR
    Updating an empty record?

    In SQL it is IS NULL, and doesn't use "=".

    Good luck

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    borrowing orange's formatting


    Code:
    UPDATE tblWHO
    SET tblWHO.Municipality_WHO = "na"
        ,tblWHO.Phone1C1 = "na"
        ,tblWHO.Phone2C1 = "na"
        ,tblWHO.FarmName = "na"
        ,tblWHO.FarmCivicAddress = "na"
        ,tblWHO.FarmCommunity = "na"
        ,tblWHO.FarmPostalCode = "na"
        ,tblWHO.Province = "na"
        ,tblWHO.Email = "na"
        ,tblWHO.Website = "na"
        ,tblWHO.Fax = "na"
        ,tblWHO.Facebook = "na"
        ,tblWHO.DateofEntry = "na"
        ,tblWHO.Notes = "na"
        ,tblWHO.RR = "na"
    WHERE (
            ((tblWHO.Municipality_WHO) is null)
            AND ((tblWHO.Phone1C1) is null)
            AND ((tblWHO.Phone2C1) is null)
            AND ((tblWHO.FarmName) is null)
            AND ((tblWHO.FarmCivicAddress) is null)
            AND ((tblWHO.FarmCommunity) is null)
            AND ((tblWHO.FarmPostalCode) is null)
            AND ((tblWHO.Province) is null)
            );
    Keep in mind this may not do what you think it will, this will ONLY update record where ALL the fields are null, if your goal is to fill in any of these fields where it's currently null with NA you'll need a different query.

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Curious is there a reason to wanting the NA instead of just leaving it as Null? So going forward you will need to run this each time you import data or someone enters a record into the table? Also for reporting and such, you could change the query to say IIF(IsNull([Field1]), "NA", [Field1]).

  8. #8
    WesHarding is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    60
    Bulzie,

    My original problem I wanted to solved stemmed from a search query I made. I would the criteria Like "*" & forms!form!txt & "*", to search through my records. I would do this for a number of different fields so it could be a little more advanced search. I found that if there was a cell that had a null value, it would not be included in the query. I didn't really know how to fix it, so this is one of my attempts at addressing the problem.

    rpeare,

    That is a very informing comment. Note taken. Do you have suggestions on alternate methods to achieve my goal? You seem to grasp the idea of what i'm trying to do

  9. #9
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Like "*" & forms!form!txt & "*" OR Is null
    Like "*" & forms!form!txt & "*" AND Not Is null

    Might be different syntax depending on where this search code is.

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    bulzie's got the right of it, you don't need to populate the fields to perform your search, use the second option in his/her post.

  11. #11
    WesHarding is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    60
    I honestly can't thank you enough. This has been a problem i've been dreading, and coming back to, since late January. Wow. So simple after the fact.

    Thank you all, so much

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-16-2017, 02:22 PM
  2. update query not updating table
    By ui7598h in forum Queries
    Replies: 2
    Last Post: 03-28-2015, 07:49 PM
  3. Thoughts on Query Design for Table Taken off of Web
    By engineer225 in forum Queries
    Replies: 1
    Last Post: 02-27-2014, 07:40 PM
  4. Update query not updating records
    By toer121 in forum Queries
    Replies: 1
    Last Post: 08-25-2011, 07:08 AM
  5. Using an update query, but updating +2 weeks...
    By AudiA4_20T in forum Queries
    Replies: 2
    Last Post: 08-02-2011, 07:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums