Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343

    Unanswered: Splitting records

    Hi Guys sorry to come to with this but - Im stuck

    Ive been haded a DB with the customer firstname prepopulated. Good stuff! However, the data punchers have put the first name AND the second name in the same record.

    I have to access the data through ODBC and have been told that I cannot change the record format to correct this (great!)

    I'm using a query, because I have to (orders are orders) to call the customer data for a number of reasons one of which is marketing letters and leaflets etc. so I need to just use the first name of the 2 in the record. Simple I used

    FirstNameOnly: Left([FIRSTNAME],InStr([FIRSTNAME]," ")-1)

    Job done until I hit the error trapping issue of no second name in the record any ideas how i do this within the query so that I get psudo=

    if 2 names show, first only, else if 1 just show first..


    does this make sense??

    Gareth

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Try this:

    Iif(InStr([FIRSTNAME]," ") > 0, Left([FIRSTNAME],InStr([FIRSTNAME]," ")-1), [FIRSTNAME])

    It should either work or set you on the right path to get it to work!

    Cheers

    ST
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Thank you very much for a prompt reply. Ill try it and let you know

    Gareth

  4. #4
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    We'll I tried and it worked .................... A DREAM! 900 names sorted out in approx 3 seconds

    Thanks for the help - Hope I can repay one day

    GAreth

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're most welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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