Results 1 to 7 of 7
  1. #1
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73

    Updating several records with new format.


    Hi, I have an excel spreadsheet with 300 part numbers in various formats i.e., some in format like 123-456-25-98, others in a similar format with dashes in various places within the 10 digit number, others with no dashes at all. I will need to import these part numbers into a database table. Rather than trying to format each field separately I would like to import the data into my access table and do an update query to have consistent formatting in all part numbers. I guess an alternative would be to change the data in excel before I import it to my database. How would the criteria field look in my query to change all records in the part numbers field to ABCD-EFG-HIJ, where the dash appears after the 4th and 7th number? Thanks.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    After the import into Access, I would modify the table by adding two new text type fields.
    First new field would be named "NoDashes" and the second new field would be named "NewFormat".
    The reason for the two new fields is to 1) keep the original data and 2) watch the progress.....

    Run an update query to remove the dashes from the original data field - update the "NoDashes" field. Use the Replace function
    Code:
    Replace(OrigField, "-","")
    Then run an update query to update the "NewFormat" field using the "NoDashes" field. The query would have a column something like
    Code:
    Left(NoDashes,4) & "-" & Mid(NoDashes,5,3) & "-" & right(NoDashes,3)

  3. #3
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73
    Thank you, that should work. I'll give it a try.

  4. #4
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73
    I added the two fields to my table. I called the first field "NPC_Old", which is the old number unformatted, and the other field "NPC_New". I then created the query on the field NPC_Old. In the criteria I wrote: Replace("NPC_Old", "-", " ") but it returned no value in the field. Am I missing something?

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The Replace command should be WITHOUT quotes. With the quotes, you are trying to replace the dash in the string "NPC_Old".
    There shouldn't be a space between the quotes in to "replace with" argument. You don't want to replace the dash with a space.
    Wrong: Replace("NPC_Old", "-", " ") <-- "NPC_Old" -- also no space between quotes
    Right: Replace(NPC_Old, "-", "") <-- NPC_Old is a string type field.

    In the attached dB, there are two queries. Run the first query and look at the table TEST.
    Then run the second query and look at the table.
    Attached Files Attached Files

  6. #6
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73
    This works great!! That's exactly what I was trying to do. Thank you for your help.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy to help...

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

Similar Threads

  1. Updating records over an API using PUT
    By mitch_pearce79 in forum Programming
    Replies: 6
    Last Post: 05-23-2016, 05:35 AM
  2. Replies: 1
    Last Post: 05-06-2016, 09:06 AM
  3. Updating Old Records Based on New Records
    By lzook88 in forum Programming
    Replies: 24
    Last Post: 09-18-2015, 09:17 AM
  4. Using a Priority and updating other records
    By Perceptus in forum Modules
    Replies: 6
    Last Post: 07-08-2014, 11:18 AM
  5. updating records to another table.
    By sankar519 in forum Access
    Replies: 1
    Last Post: 06-30-2014, 08:31 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