Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Record management (ACC-97 SR2)

    I need to parse data from one field into other fields.

    I've attached a sample table exported to Excel, that has a hilighted, filtered column.
    The value in the column [TempName] is an InStr value from the field [Name] in an Access table.

    Name (attached sheet) has all data intact!
    I want to parse it out to separate fields...

    my approach:
    I need to update all records that have a non-null value in [TempName] (I filtered out the non-nulls)
    to update field [Next] to be the results of field [Next2] and parse everything after the hyphen out to field [office]

    I know this is easier than I'm making it but It's frustrating because, I can see it in my head but I can't explain it.

    Separate those with a last name from those with last name and office.
    How can this be done?
    Is this the best way to parse out data?
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record management (ACC-97 SR2)

    It looks like you are parsing correctly for the most part. If you want to omit the hyphen in OFFICE, add 1 to the value in TEMP similar to this example.

    OFFICE: Mid([Next],[Temp Name]+1)

    This will start the parse 1 character past the hyphen. HTH.

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record management (ACC-97 SR2)

    Approach 2
    OK, Let's try this another way...
    How about a conditional statement?
    How would I write an IIF statement that first reads the string then determines
    if there is the desired char (i.e. "-") in the string.
    If so, run, the parse
    else skip to the next record

    I've expanded the query to test for the InStr value to be >0
    If so, the record is true, else false (IIF)
    Now, all I need to do is run the parse on those that test as true or [Test]= true

    Is this type of process do-able in a query or will this call for a procedure that writes an entire table from scratch? <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record management (ACC-97 SR2)

    jazman2001,

    Okay, if I understand your dilemma correctly, you're looking for an UPDATE query that will replace the NAME-OFFICE information in the field with just the NAME information. If this is correct, then something like

    UPDATE [NameTable] SET [NameTable].Next = IIf(0<InStr(1,[Next],"-"),Left([Next],InStr(1,[Next],"-")-1),[Next]);

    should work.

    The iif statement first checks if there's a hyphen in the string (instr() returns zero if string2 not found in string1). If so, then it determines where the hyphen is located (again using the instr() function, since if string2 is found instr() returns the position in string1 where it's located). This position (minus 1) is used as the second argument of the left(string, length) function. If a hyphen is not found, then the whole name is retained.

    Since this is an UPDATE query, the result of all this will replace the original "Next" field value, record-by-record.

    Of course, if any of your names have hyphens in them (e.g., "ZETTA-JONES - ATTORNEY" then this scheme won't work -- you'll just get "ZETTA").

    Hope this helps.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record management (ACC-97 SR2)

    Curious...

    I took your Excel table and imported it into Access (I imported just the "Next" field and added a primary key ID field) and ran the query as shown above. The updated table had just the person's name as the value of the "Next" field (without the hyphen nor occupation). This is what you want, right? I copied the SQL code from Access and pasted it directly into the Lounge reply so there shouldn't be a typo. I am using Access 2000, but upon your reply I tried it in Access 97 and it worked there, too.

  6. #6
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record management (ACC-97 SR2)

    Well, It looked good on paper... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    I experimented with several variants of this and still got no results.
    Perhaps this is too complicated a script to wrap into an IIF statement.
    Microsoft has included a disclaimer for this...

    <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q106/2/88.asp?LN=EN-US&SD=gn&FR=0&qry=IIF&rnk=1&src=DHCS_MSPSS_gn_SRCH &SPR=ACC97>http://support.microsoft.com/support/kb/ar...H&SPR=ACC97</A>

    There is however an entirely different way of doing this:
    Divide and conquer
    LName: Left([Next],[Temp Name]-1)

    Then a simple IIF statement: Last: IIf([Temp Name]>0,[LName],[Next]) <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

  7. #7
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record management (ACC-97 SR2)

    I appreciate the effort and the code was way cool!!
    I can't understand why i didn't work...
    I'll keep trying.
    One thing, when I printed it out I got
    "0&lt(InStr...)
    instead of
    0<(InStr...)
    I'll keep trying the code...but I took another road
    Thanks again.

Posting Permissions

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