Results 1 to 4 of 4
  1. #1
    boknoval is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2012
    Posts
    2

    How to set a format on strings upon data import

    I have a database where I need to import data from an excel file.
    What my employer needs me to do is upon appending the data to a table which I named properties, under the field which I called owner, all the names should follow the correct format. Most of the inputted data does not follow the correct format.

    The correct format of the names should be:
    K. CURTIZ -> Where K is the initial of the First Name, it should have the '.' and a space before the Family Name

    Here's an example of a data that is inputted incorrectly:
    R OTTESON -> it should be R. OTTESON

    another example where there are 2 names of 2 different persons, it should be inputted this way:
    A. & J. PANAG

    Mostly inputted incorrectly this way:


    J&T BOTIFAN -> it should be J. & T. BOTIFAN

    I hope somebody can help me with this.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,615
    That is not setting a format, that is modifying the data.

    You can do Find/Replace actions. Replace space with '. '

    Replace & with '. & '

    This might be able to be programmed but I've never done that and gets tricky. Would not want to modify 'Carter & Sons' with either of the above.

    Think you are going to have to do manual edit and review each replace for correctness.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    boknoval is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2012
    Posts
    2
    oops sorry about that.. I'm not well versed with what the process is called.. hahaha

    Ive tried that already and I think that wouldn't work.
    you see some names have two initials, for example this data: J&T BOTIFAN -> it should be J. & T. BOTIFAN



  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,615
    Right, J&T BOTIFAN would require both of the replacements. The first one to make T into 'T. '. Then the second to make & into '. & '
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Data Entry Format
    By jlgray0127 in forum Forms
    Replies: 7
    Last Post: 10-31-2011, 04:04 PM
  2. Replies: 3
    Last Post: 06-20-2011, 03:09 PM
  3. Custom Data input mask or format?
    By RiverAnimal in forum Database Design
    Replies: 2
    Last Post: 12-06-2010, 09:58 AM
  4. format of text data type
    By frcastro in forum Access
    Replies: 4
    Last Post: 06-23-2010, 09:36 AM
  5. Please Help, How Do I Format this Data?
    By txdave36 in forum Access
    Replies: 6
    Last Post: 09-09-2009, 04:31 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