Splitting a field in a query 2

[NoelMG]

Hi guys and gals,

I'm trying to split a field down in a query and to be quite honest don't know where to begin!

The field I am splitting is called "Operator", and some of the records have a comma in them and some not. I would like to split after the comma.

For instance, I may have

British Airways
Air France, stored
Lufthansa, wfu
Iberia

Can anyone help me please? I'm guessing an Iif statement needs to pop in somewhere but other than that I'm at a loss!!

Thanks every so much,

Noel.

 

[Golom]

You need another table (called "Integers" in the following example) with a field num containing the integers from 1 to 2 more than the longest string that you want to parse. You then use SQL like this:
[tt]
SELECT I1.KeyCol, Mid$("," & I1.instring & ",", S1.num + 1 , S2.num - S1.num - 1) AS [SubString]

FROM InputStrings AS I1, integers AS S1, integers AS S2

WHERE Mid$("," & I1.instring & "," ,S1.num , 1 ) = ','
and Mid$("," & I1.instring & "," ,S2.num , 1 ) = ','
and S1.num < S2.num
and instr(1,Mid$("," & I1.instring & ",", S1.num + 1 , S2.num - S1.num - 1),",")=0;
[/tt]
where InputStrings is the table that contains your field and instring is the field with the comma-separated values.

 

[TonyJollans]

Hi NoelMG,

The function you need to use is InStr. This finds the position of one string within another; when you have that you know where to break the string. To get the portion before the comma ..

[purple]=Left([Operator],InStr([Operator],",")-1)[/purple][green]  ' Returns "Air France"[/green]

To get the portion after the comma ..

[purple]=Mid([Operator],InStr([Operator],",")+1)[/purple][green]  ' Returns " stored"[/green]

Now, if there isn't a comma, the Instr function will return zero which will cause a problem so needs to be trapped, perhaps like this for the bit before the comma ..

[blue]=If(InStr([Operator],",")>0,Left([Operator],InStr([Operator],",")-1),[Operator])[/blue]

Finally, you may want to lose leading spaces; I leave you to add an LTrim function to do this.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[NoelMG]

Hi Tony,

Does this need to go into the actual query? I've tried this, only when I run the query I get:

Compile error in query expression '=Mid([Operator],InStr([Operator],",")+1)'

Thanks!

Noel

 

[scriverb]

Give this a try:

SELECT A.[Operator], IIf(InStr(1,A.[Operator],",")=0,A.[Operator],Mid$(A.[Operator],1, InStr(1,[tblCustomers]![Cust_Name],",")-1)) AS Name_1, IIf(InStr(1,A.[Operator],",")=0,"", Mid$(A.[Operator],InStr(1,A.[Operator],",")+2)) AS Name2 
FROM [red]YourTableName[/red] as A;

This should give you the data parsing as you requested.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[TonyJollans]

Hi Noel,

It should be able to go straight in a Query ..

[blue][tt]SELECT Mid([Operator],InStr([Operator],",")+1) FROM MyTable;[/tt][/blue]

.. works just fine for me. I don't understand the error. Could you post the whole SQL statement please? And, just to be sure, confirm how you're running it.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[NoelMG]

Hi Tony,

the full code in the query is as follows:

SELECT UPDATES2.cn, UPDATES2.type, STATUS.type, UPDATES2.model, UPDATES2.exreg, STATUS.status, UPDATES2.Field6 AS Operator, Mid([Operator],InStr([Operator],",")+1) AS Expr1
FROM UPDATES2 LEFT JOIN STATUS ON UPDATES2.cn = STATUS.cn
WITH OWNERACCESS OPTION;

Thanks!

Noel.

 

[TonyJollans]

Hi Noel,

The SQL should be OK, and works for me.

I didn't think I'd be able to do this, but I tried coding a Function called InStr and deliberately put an error in it. This caused me a similar message to the one you report, so the question must be - Do you have any User-defined Functions called Mid or InStr?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[NoelMG]

Fixed it! I had a missing reference in the VBA code which I removed, and hey presto it works a dream!

Thanks all!!

 

[scriverb]

TonyJollans: I think NoelMG was putting the equals(=) sign in the query. I know you meant that to ge put in a control source property but it was misintpreted and put right into the query. I think he has since changed it when put into the SQL that was posted.

Both of our approaches work but I think you meant to demo an IIF function statement rather than an IF statement. That could also be causing a problem in the query.

NoelMG did you try running the SQL that I supplied.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[TonyJollans]

Good catch, Bob. You're absolutely right - IIf, not If.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.