Using Split Function in a Query

[slenish]

Hi everyone,

Im trying to take one column (in my query) and split the data in four different columns. so far i have the first part of the information and the last part of the information to split fine. I just cant get the middle part to split right.

Here is what im trying to split.

IDDW - Livermore, CA 94550

Every record in the table is the same so i want it to split at the - then at the first , and then at the space so you would have four fields that would say

Column1.Column2....Column3....Column4
IDDW.... Livermore.......CA ..........94550

Here is what i have so far

Exp1: Left([Shipper Reference#],InStrRev([Shipper Reference#]," - ")-1)
Exp2: Right([Shipper Reference#],Len([Shipper Reference#])-InStrRev([Shipper Reference#]," "))

I added it to to more columns and keep trying to change these in hopes to get something but im having no luck so far.

Thanks for the help.

[gershwyn]

In situations like this, I always find it helpful to write a quick function to do the work for me. That way you don't have to squeeze everything on to a single line.

Function SplitRef(SplitString As String, Section As Integer)
  Select Case Section
    Case 1:
      StartPos = 1
      EndPos = InStr(SplitString, "-") - 2
    Case 2:
      StartPos = InStr(SplitString, "-") + 2
      EndPos = InStr(SplitString, ",") - 1
    Case 3:
      StartPos = InStr(SplitString, ",") + 2
      EndPos = InStrRev(SplitString, " ") - 1
    Case 4:
      StartPos = InStrRev(SplitString, " ") + 1
      EndPos = Len(SplitString)
  End Select
  SplitRef = Mid(SplitString, StartPos, EndPos - StartPos + 1)
End Function

1st:

?SplitRef("IDDW - Livermore, CA 94550", 1)
IDDW
 

2nd:

?SplitRef("IDDW - Livermore, CA 94550", 2)
Livermore
 

3rd:

?SplitRef("IDDW - Livermore, CA 94550", 3)
CA

4th:

?SplitRef("IDDW - Livermore, CA 94550", 4)
94550

This works with the example given, but could benefit from some added error checking.

[Jim Doherty]

In response to gershwyns excellent posting ... (if you are not too familiar with the mechanics of how to implement this function) once you have created the function in a VBA module you call it via the query grid and the SQL for the query will look like the below where the reference YourTableName= the actual name of your table.

Gershwyns post demonstrates how to create the function. You can then simply call it as you would any other inbuilt Access function.

SELECT SplitRef([Shipper Reference],1) AS Column1,
       SplitRef([Shipper Reference],2) AS Column2,
       SplitRef([Shipper Reference],3) AS Column3,
       SplitRef([Shipper Reference],4) AS Column4
FROM YourTableName;

[slenish]

Wow really appreciate the help from both of you.

Well i got it to work but there was a debug error with this line

 SplitRef = Mid(SplitString, StartPos, EndPos - StartPos + 1)
 

any idea why?

Thanks again

[Jim Doherty]

@slenish
Is your data consistent? my guess is that it is not. The function will not deal with all eventualities of inconsistent data appearing in your text string. The function itself is explicit for your given example pattern.

This is why error checking within the function for zero length strings nulls and other anomolies passed in is important where it does not follow the pattern you describe.

There are many purpose built string manipulation functions available LEFT,RIGHT, INSTR, MID, SPLIT and so on, but all rely on specifics and arguments in order to delimit and parse data into separate entities.

Have a look at your data first then understand the concept of the function as contributed particularly the startpos and endpos variables, These variables are examining the position of a single character within the string at a given point in your data string and adding or substracting numeric values in order to set the position. If the values do not exist in the data then the return value for the INSTR function will be 0 therefore subtracting -1 would not make sense.

'What if' scenarios that undermine your function have to be neutralised and can be done once any problems your data may pose has been considered.

[NeoPa]

any idea why?

I suspect one of the values in that line reflects data that doesn't match the expected format.

Did you know that for most simple variables (objects can be more complicated but their default properties often show up ok) you can hover over them with the mouse when code execution is paused and the value will display in a tool-tip type bubble (See Debugging in VBA). If you examine the data you will understand exactly what's wrong (at least if not, you can tell us the values & we'll explain).