How to normalize/parse fields? Problem with Instr() syntax

[sarajini]

Hi!
I plan to import an Excel spreadsheet that is not normalized. I am wondering how I should go about splitting a field.

My Excel sheet has a column for Advocates. A case can have either one or two advocates. If a client has two advocates, they are both listed in the same column. The names are separated by a slash, like this:

Advocate(s):
Smith
Jones
Smith/Jones
Smith/Roberts

What is the best way to go about normalizing this data? So far, I am planning to:

1. Split Advocate information into two fields, Advocate1 and Advocate2.
2. Combine the two fields into one, Advocate. This would be different from the original advocate field, because each cell (“control”?) would contain only one Advocate.
3. Create a table with CaseNumber (the primary key for cases) and each case’s corresponding Advocate.

Does that sound reasonable?
Right now I’m stuck on Step 1. I don’t know how to use InStr() in the code that would split Advocate into two fields. After studying posts on parsing, here is my best shot at it:

UPDATE import_table
SET Advocate1=Left$(Advocate, instr(Advocate, “/”))
SET Advocate2=Right$(Advocate, instr(Advocate, “/”));

Of course, I’m getting error messages...(“syntax error: missing operator”)
If anyone could help me fix the code, I would really appreciate it! Any advice on the other steps in normalization would also be quite helpful.

Thanks, Tek-Tips folks!
sarajini

 

[Maquis]

You're on the right track, you just have the syntax a bit off. I've used the same code and it can be difficult to get the syntax sometimes. Try this.

Set Advocate1 = Left$(Advocate, InStr(1, Advocate, "/&quot - 1)
Set Advocate2 = Right$(Advocate, Len(Advocate)-InStr(1, Advocate, "/&quot)

Maq
<insert witty signature here>

 

[sarajini]

Hi Maquis,
That code looks good. Unfortunately, Access is still giving me problems with it...

Here's what I entered in sql view:

UPDATE mock_import_table 
SET Advocate1 = Left$(Advocate, InStr(1, Advocate, "/") - 1)
SET Advocate2 = Right$(Advocate, Len(Advocate)-InStr(1, Advocate, "/"));

When I tried to run it, I was told
&quot;Syntax error: missing operator in query expression,&quot; then it restated the code from

Left$

to the end.

Hmmm...I checked the Advocate field in the original mock_import_table, and there doesn't seem to be any irregularities there. Maybe Access is having problems because some records only have one advocate, instead of two divided by slashes?

thanks, Maquis!!!
sarajini

 

[Maquis]

Oops, yep you're right. That code assumes that all your fields have slashes in them.

Try this:

Set Advocate1 = Iif(Instr(1,Advocate,&quot;/&quot,Left$(Advocate, InStr(1, Advocate, &quot;/&quot - 1),Advocate)
Set Advocate2 = Iif(Instr(1,Advocate,&quot;/&quot,Right$(Advocate, Len(Advocate)-InStr(1, Advocate, &quot;/&quot,Null)

Maq
<insert witty signature here>

 

[sarajini]

Hi,
Sorry to bother you again...
but this code is still giving me a syntax error : (

UPDATE mock_import_table 
Set Advocate1 = Iif(Instr(1,Advocate,"/"),Left$(Advocate, InStr(1, Advocate, "/") - 1), Advocate)
Set Advocate2 = Iif(Instr(1,Advocate,"/"),Right$(Advocate, Len(Advocate)-InStr(1, Advocate, "/"),Null);

thanks again,
Sarah

 

[Maquis]

What error is it giving you?

I may have misplaced a parenthesis somewhere. I'm not used to working with Iif() functions. If it were my project I probably would have created a form and placed VBA code in the form to break apart the fields. I've also been lousy at writing SQL code and better with VBA code.

Maq
<insert witty signature here>

 

[sarajini]

Hi Maq,
I get this message:
&quot;Syntax error (missing operator) in query expression,&quot; then it restates the code from the first

Iif

to the end.
I don't know how helpful that is to you.

Thanks for trying to work with me on this! You've already gotten me so much further with the code than I would have otherwise.

--sarajini

 

[GoDawgs]

Two easy things...just to make you kick yourself...you're missing a parentheses at the end, and also take out the second &quot;SET&quot; and replace it with a comma...:

UPDATE mock_import_table
Set Advocate1 = Iif(Instr(1,Advocate,&quot;/&quot,Left$(Advocate, InStr(1, Advocate, &quot;/&quot - 1), Advocate), Advocate2 = Iif(Instr(1,Advocate,&quot;/&quot,Right$(Advocate, Len(Advocate)-InStr(1, Advocate, &quot;/&quot,Null));

Hope that helps.

Kevin

 

[sarajini]

Hi Kevin!
Ugh, Access…the code’s still being balky.
I entered what you had written. When I tried to run the query, I was asked for parameters for Advocate1 and Advocate2.
The recordset had fields for Advocate1 and Advocate2, but the fields were all blank.
When I tried to run the query from database view, I was told:
“Wrong number of arguments used with function in query expression.” Then it restated from the second

Iif(

to the end.

Thanks so much for your time!
sarajini