How do I use the Split Function?

[slenish]

Hello all,

I am trying to use the split function to seperate out some information in my table. I keep reading up on how to get this to work but every time i try it its not working. I either get errors or a pop up box asking me to enter in a value and when i do i just get zeros back.

What I have is a report I get sent that is in Excel format. Imported it in to a table in Access so I can do more with it. One of the columns has two pieces of information in it that I want to seperate in to two seperate columns.

What I did was I made a query that runs off of the table. In the query in the Field box I pluged in this code

Expr1: Val(Left([SHIPPER REFERENCE#],InStr([SHIPPER REFERENCE#]," ")))

Im not sure if i am putting this in the wrong spot or i have something typed wrong but any help would be great. Also i have tried various additions by putting a +1 or a -1 in at the end and still nothing.

[NeoPa]

This doesn't look too bad, but you haven't handled the situation where there is no space in the record.

[slenish]

Hi NeoPa,

What do you mean by no space?? In the part where it says Shipper Referene? Should that be together? Because I have tried that as well.

The way im trying to seperate the information that is in the table is by the space that is between to two parts of information. I have read you can have the left and right split look for the space is there is not a comma or apostrophe, but I cant get it to work right.

Any examples you could show me would be greatly appreciated :D

[missinglinq]

Assuming that there is only the single space in the original field

FirstField =  Left(Me.OriginalField, InStr(Me.OriginalField, " ") - 1)
SecondField = Right(Me.OriginalField, Len(Me.OriginalField) - InStr(Me.OriginalField, " "))

Linq ;0)>

[slenish]

Hi Linq,

There are two spaces in the original field. I was wanting to seperate them by the second space.

the information is displayed like this

Box 1 ID12345

I want to take it and seperate it so Box 1 goes in to one column and ID12345 goes in to another. The reason I want to seperate it by the second space is because the Box # could change and become two or three numbers such as
Box 1
Box 12
Box 123

But the last part does not change as far as the amount of characters. although the ID number and the letters ID can change.

[missinglinq]

That's simple enough to do! You just have to replace the InStr() function with the InStrRev() function. The former searches from the beginning of a string, looking for the first instance of a search string.The latter starts at the end of the string and works backwards looking for the first instance

FirstField = Left(Me.OriginalField, InStrRev(Me.OriginalField, " ") - 1)
SecondField = Right(Me.OriginalField, Len(Me.OriginalField) - InStrRev(Me.OriginalField, " ")).

Linq ;0)>

[NeoPa]

I'm guessing Linq is right in that InStrRev() would indeed be the more appropriate function call for the scenario you describe. His suggestion is in VBA though, and it appears your question is query related so it may not be a perfect fit.

It's hard to make a simple suggestion though, as you don't explain what is wrong with your original code. You simply imply it doesn't do exactly as you'd expect. This is very little information to work with.

My previous comment, by the way, was a reference to the data that you are processing. Surely it would work to an extent if the data in every record contained at least one space. I was asking if there may be some records with no spaces in. I was trying to guess what your actual problem was as you hadn't explained it in your question.

I'm sure with some clearer information from you we can suggest a solution you can use from within your query. My guess is that it will be something very similar to what Linq's already suggested, but in SQL or query form.

[missinglinq]

You're right, NeoPa! In the Query Grid the calculated fields would be

FirstField: Left([OriginalField],InStrRev([OriginalField]," ")-1)
 
SecondField: Right([OriginalField],Len([OriginalField])-InStrRev([OriginalField]," "))

In SQL it would be

SELECT YourTableName.OriginalField, Left([OriginalField],InStrRev([OriginalField]," ")-1) AS FirstField, Right([OriginalField],Len([OriginalField])-InStrRev([OriginalField]," ")) AS SecondField
FROM YourTableName;

Linq ;0)>

[slenish]

NeoPa, and Linq

Really appreciate the help guys :D I dont know why exactly it was not working with the way I did it the first time. I took linq's last posting example(the first one not the SQL one) and re-applied it to my query and for some reason it worked perfectly this time.

The last time i just kept getting errors either asking for the parameter value, or i would get a debug error, or it would be syntax error. Dont know why i kept getting that before and this time it was fine.

Thanks so much!!

[NeoPa]

I can see nothing in your posted code that would explain those types of errors, but with the data you describe, Linq's code is certainly what you's need to use in place of your original code.