Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2010

    Unanswered: Extracting string within a string

    Hello, I am trying to extract a string of characters from a field titled, "FullName" using an append query. I want this string of characters to be exported to a new field called, "LName."

    This is my code:
    LName: Mid([FullName],InStr([FullName]," "),InStr(1,[FullName],",")-InStr(1,[FullName]," ")-1)

    When I run the query I get a type conversion failure.

    This is an example of what I want to do:
    The value for "FullName" is "Jim Smith, owner".
    After running the query, I want "LName" to be "Smith"

    Many thanks for your help.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    right([FullName],instrrev([FullName]," "))
    Have a nice day!

  3. #3
    Join Date
    Apr 2005
    Zagreb - Croatia
    Look at "DemoExtractStringA2002.mdb" (attachment, zip).
    Look at Table1, Form1. Open Form1 and see.
    Adapt it in your mdb.
    Attached Files Attached Files
    Last edited by MStef-ZG; 12-15-10 at 06:59.

  4. #4
    Join Date
    Dec 2010

    Thanks for the suggestions. I tried this and it does not work. This is how Access split these two entries:

    Example 1
    FullName: Jon Sade, Mic Inc.
    LName: n Sade, Mic Inc.

    Example 2
    FullName: Robert Johns, Sr. Tech
    LName: bert Johns, Sr. Technican

    Any suggestions?
    Last edited by adevries; 12-15-10 at 12:19.

  5. #5
    Join Date
    Dec 2010

    much Prodisio

    To MStef-ZG, I don't want to open unknown files on the web. If you write your code in this forum then I might try it.

  6. #6
    Join Date
    Jul 2006
    If you are on a Win 7 system read my post today. Try running your query on a Win XP system. I think MS did an update that disabled the functions you are using (mid right left etc.)

  7. #7
    Join Date
    Dec 2010

    Nateely 26k

    Does anyone know of a patch to repair MS Access so that it runs properly on Windows 7? Switching operating systems is not a viable option.
    Last edited by adevries; 12-15-10 at 17:44.

  8. #8
    Join Date
    Dec 2010
    I don't know about the idea that Win7 disables the functions mid right left. Thought I would test it, in code anyways. I run the Windows 7 Ultimate and Access 2007 and tried the Mid function. It seems to work just fine.

    In all fairness, I did not read jimgriggs post regarding this issue and there most like is some issue there since nothing surprises me with regards to Microsoft.

    Also, I never did try it in a query but if it's a real issue then perhaps try to utilize the idea of calling a User-Defined function from within your query to handle the job. Check the following Link:

    Calling user-defined function from query - Microsoft Access / VBA answers

    Hope this helps

  9. #9
    Join Date
    Nov 2010
    LastName: Trim(Mid(Left([FullName],InStr([FullName],",")-1),(InStr(Left([FullName],InStr([FullName],",")-1)," "))))

    FirstName: Trim(Mid([FullName],1,InStr(Left([FullName],InStr([FullName],",")-1)," ")))

    Rest: Mid([FullName],InStr([FullName],",")+2)
    Last edited by eremija; 12-19-10 at 05:56.

  10. #10
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Parsing things like names and physical addresses frequently becomes a mare's nest, simply because there are so many variations in the formats for them!

    You need to be aware that eremija's code will work if the FullName field is always entered in the exact way your examples were entered!

    Using Jon Sade, Mic Inc. his LastName expression will yield Sade.

    But if the FullName was entered as Jon J. Sade, Mic Inc., including a middle initial, the expression would yield J. Sade.

    If the FullName was entered as simply Jon J. Sade, without the suffix Mic Inc. the expression would yield nothing, but rather would pop a Runtime error.

    As to the question of Windows 7 causing Mid() to not work, I'm pretty sure that's not the case. This kind of problem, with Mid() as well as Left(), Right() etc. not working is always the result of a missing reference. When reported as happening when an app is moved to a new OS or to a new machine with a different OS what is actually happening is that Access has been installed on the system after the OS and the new installation of Access doesn't have the missing library installed.

    Linq ;0)>
    Last edited by Missinglinq; 12-19-10 at 12:42.
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  11. #11
    Join Date
    Dec 2010
    I ran the code that eremija created and it worked, although I received this error message:

    Microsoft Access set 1 field to Null due to a type conversion failure, and it didn't add 0 records to the table due to key violations, 0 records due to lock violations, and 0 records due to validation rule violations.

    I chose to ignore the errors and run the query anyway, and it worked!
    There is only one record that did not work correctly. This record was unusual because it was not formatted as "FName Lname, description." The record was "Nebraska owner" and the field was split into FName: "Nebraska" and LName: "ebraska owner."

    Many thanks to everyone for looking at this problem!

Posting Permissions

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