By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,089 Members | 2,069 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,089 IT Pros & Developers. It's quick & easy.

Using Split Function in a Query

P: 283
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

IDDW.... Livermore.......CA ..........94550

Here is what i have so far
Expand|Select|Wrap|Line Numbers
  1. Exp1: Left([Shipper Reference#],InStrRev([Shipper Reference#]," - ")-1)
  2. 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.
Apr 27 '10 #1
Share this Question
Share on Google+
5 Replies

P: 122
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.

Expand|Select|Wrap|Line Numbers
  1. Function SplitRef(SplitString As String, Section As Integer)
  2.   Select Case Section
  3.     Case 1:
  4.       StartPos = 1
  5.       EndPos = InStr(SplitString, "-") - 2
  6.     Case 2:
  7.       StartPos = InStr(SplitString, "-") + 2
  8.       EndPos = InStr(SplitString, ",") - 1
  9.     Case 3:
  10.       StartPos = InStr(SplitString, ",") + 2
  11.       EndPos = InStrRev(SplitString, " ") - 1
  12.     Case 4:
  13.       StartPos = InStrRev(SplitString, " ") + 1
  14.       EndPos = Len(SplitString)
  15.   End Select
  16.   SplitRef = Mid(SplitString, StartPos, EndPos - StartPos + 1)
  17. End Function
Expand|Select|Wrap|Line Numbers
  1. ?SplitRef("IDDW - Livermore, CA 94550", 1)
  2. IDDW
Expand|Select|Wrap|Line Numbers
  1. ?SplitRef("IDDW - Livermore, CA 94550", 2)
  2. Livermore
Expand|Select|Wrap|Line Numbers
  1. ?SplitRef("IDDW - Livermore, CA 94550", 3)
  2. CA
Expand|Select|Wrap|Line Numbers
  1. ?SplitRef("IDDW - Livermore, CA 94550", 4)
  2. 94550
This works with the example given, but could benefit from some added error checking.
Apr 27 '10 #2

Jim Doherty
Expert 100+
P: 897
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT SplitRef([Shipper Reference],1) AS Column1,
  2.        SplitRef([Shipper Reference],2) AS Column2,
  3.        SplitRef([Shipper Reference],3) AS Column3,
  4.        SplitRef([Shipper Reference],4) AS Column4
  5. FROM YourTableName;
Apr 28 '10 #3

P: 283
Wow really appreciate the help from both of you.

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

Expand|Select|Wrap|Line Numbers
  1.  SplitRef = Mid(SplitString, StartPos, EndPos - StartPos + 1)
any idea why?

Thanks again
Apr 28 '10 #4

Jim Doherty
Expert 100+
P: 897
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.
Apr 28 '10 #5

Expert Mod 15k+
P: 31,494
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).
Apr 28 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.