Results 1 to 6 of 6
  1. #1
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188

    Cannot update simple field data

    Attached is an MS Access 2019 database. I'm hoping someone can download and let me know why I cannot update the OrderImports table. See update query. It appears the values match, but will not update.

    However, if I manually cut-n-paste the values from ShipCodes into OrderImports, it would update.

    I must be missing something simple.

    Thanks!
    Attached Files Attached Files

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    One very simple thing you've missed is that the update query contains no records for updating
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    I'm really not seeing what I'm getting wrong here. See the matching data below.
    John Doe ... NDS ... ND is a match to FedEx ... ​NDS ... ​ND ... FedEx Next Day ​Standard ... FDEX
    etc.

    What am I missing?

    Table: OrderImports
    ShipToName ... ShipMethod ... ShippingSpeed
    John Doe ... NDS ... ND
    Jane Doe ... NS ... G2
    Bill Doe ... NS ... SC

    Table: ShipCodes
    Carrier ... ShipMethod ... ShippingSpeed ... Descrip ... Abbrev
    FedEx ... ​NDS ... ​ND ... FedEx Next Day ​Standard ... FDEX
    FedEx ... ​NS ... ​G2 ... ​FedEx Ground ... FDXG
    FedEx ... ​NS ... ​SC ... ​FedEx 2 Day ... FX2D

  4. #4
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Quote Originally Posted by shank View Post
    I'm really not seeing what I'm getting wrong here. See the matching data below.
    John Doe ... NDS ... ND is a match to FedEx ... ​NDS ... ​ND ... FedEx Next Day ​Standard ... FDEX
    etc.

    What am I missing?

    Table: OrderImports
    ShipToName ... ShipMethod ... ShippingSpeed
    John Doe ... NDS ... ND
    Jane Doe ... NS ... G2
    Bill Doe ... NS ... SC

    Table: ShipCodes
    Carrier ... ShipMethod ... ShippingSpeed ... Descrip ... Abbrev
    FedEx ... ​NDS ... ​ND ... FedEx Next Day ​Standard ... FDEX
    FedEx ... ​NS ... ​G2 ... ​FedEx Ground ... FDXG
    FedEx ... ​NS ... ​SC ... ​FedEx 2 Day ... FX2D
    Addendum: This is the update query:
    UPDATE OrderImports INNER JOIN ShipCodes
    ON (OrderImports.ShipMethod = ShipCodes.ShipMethod) AND (OrderImports.ShippingSpeed = ShipCodes.ShippingSpeed)
    SET OrderImports.ShipMethod = [ShipCodes]!Carrier, OrderImports.ShippingSpeed = [ShipCodes]!Abbrev;

    My intent is to replace the values in OrderImports.ShipMethod wigh [ShipCodes]!Carrier.
    And OrderImports.ShippingSpeed with [ShipCodes]!Abbrev


    Thanks!

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    looks to me like you have hidden characters in one table/field or the other

    Do the following

    open shipcodes - sort the shipmethod column and you get this

    ShipMethod
    ​ES
    ​ES
    ​ESS
    ​HD
    ​HDS
    ​NDS
    ​NDSS
    ​NS
    ​NS
    ​NS
    ​NS
    ​PON
    ​PONS
    ​SG
    ​SG
    ​SG
    ​SG
    ​SG
    ​SMP
    ​SMPU
    ESS
    MI
    MP
    NDS​
    NDSS​
    NS
    NS​​
    NS​​
    NS​​
    SG​
    SG​​
    SG​​
    SP

    as you can see at the bottom, the data is not alphabetic. The same applies to the shippingspeed column

    So I would check where you have got this data from. Alternatively It is always possible the data has become corrupted. I tried compact repair - that did not work, which implies you have an issue with your data

  6. #6
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Ugh! That's the problem. See attached screenshot. I cut-n-pasted off a website, then used TRIM in Excel. But that wasn't enough to remove hidden characters.

    Thanks!
    Attached Thumbnails Attached Thumbnails S1.jpg  

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

Similar Threads

  1. Replies: 8
    Last Post: 08-16-2017, 10:53 AM
  2. Replies: 1
    Last Post: 05-31-2015, 09:20 AM
  3. Replies: 3
    Last Post: 07-30-2013, 12:11 PM
  4. Simple Update SQL
    By DADAZHU in forum Programming
    Replies: 4
    Last Post: 12-08-2011, 05:22 PM
  5. Update Data, and set field
    By adams.bria in forum Queries
    Replies: 1
    Last Post: 11-16-2011, 04:30 PM

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