Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2003
    Location
    Oxford, UK
    Posts
    6

    Angry Unanswered: Deleting data in fields after a common delimeter

    I have a problem with some data we hold. We have a fullname field but require a title field as well. I need to find a way to delete all data in the fullname field after a common delimeter (a space). Being a title the size ranges from 2 to 9 characters so a 'Left' select will not work.
    The data is held in Access 2000. Any help would be much appreciated.
    Cheers
    Tom

  2. #2
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91

    Re: Deleting data in fields after a common delimeter

    Originally posted by Talltom
    I have a problem with some data we hold. We have a fullname field but require a title field as well. I need to find a way to delete all data in the fullname field after a common delimeter (a space). Being a title the size ranges from 2 to 9 characters so a 'Left' select will not work.
    The data is held in Access 2000. Any help would be much appreciated.
    Cheers
    Tom
    Hi, Tom!

    Could you give us an example ?
    My environment: Windows XP/ Access 2000 - Using Microsoft DAO 3.6 Library

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i'm not certain if this would work in SQL but it will in VB

    <field> = left(<sourcedata>,instr(<sourcedata>," "))
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    SELECT Left(<sourcedata>,InStr(<sourcedata>," ")-1) AS <what ever>
    FROM <where ever>;


    correction i am now certain that it works in SQL
    Last edited by m.timoney; 11-18-03 at 11:30.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    Sep 2003
    Location
    Oxford, UK
    Posts
    6
    I'm starting to feel really stupid because even I've thought of a solution.. All you have to do is export data to text and re-import as space delimited... Easy now I think about it... (Especially as I know nothing about VB)

  6. #6
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    try my second suggestion which you probably missed as your post is less than a minute after mine
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  7. #7
    Join Date
    Sep 2003
    Location
    Oxford, UK
    Posts
    6
    Thanks all, I'm going try your ideas just so next time I'll know.
    Tom

  8. #8
    Join Date
    Sep 2003
    Location
    Oxford, UK
    Posts
    6
    Originally posted by m.timoney
    i'm not certain if this would work in SQL but it will in VB

    <field> = left(<sourcedata>,instr(<sourcedata>," "))
    I'd like to give this a go go but I don't know how to build the VB. Do I just need to give it a function name and run it (via a query)?

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    try the second option as it's hell of a lot simpler to implement

    but if you do want to try my first option then the function should look something like

    Code:
    '******** WARNING UNTESTED CODE ************
    
    dim rs as DOA.recordset
    set rs = currentDB.openRecordset("<table Name>")
    
    while not rs.eof
    rs("<field Name>") = left(<sourcedata>,instr(<sourcedata>," "))
    rs.movenext
    wend
    the <sourcedata> depends of where it's coming from so might be a rs(fieldname) a text box or what ever
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  10. #10
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Post ok try this

    mid(USR_N,1,instr(1,USR_N," ")-1) as namePart 'this will give the first part of the name

    so if you have Do John it will give you "Do"


    so in your case


    Title & mid(USR_N,1,instr(1,USR_N," ")-1) will give "Sir. Do"

    if it's the other way around so John Do give a holler

    Good luck

  11. #11
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    correct me if i'm wrong Marvels but isn't you method identical to mine only using mid instead of left?
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •