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

    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 Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    4,070
    One very simple thing you've missed is that the update query contains no records for updating
    Colin (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

  3. #3
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    136
    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
    136
    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
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,029
    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
    136
    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
  •  
Tech Forums: Microsoft Office Forums