Record management (ACC-97 SR2)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

Well, It looked good on paper…
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…

http://support.microsoft.com/support/kb/ar…H&SPR=ACC97

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])

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote