find position no of a character in a text string

[SimoK]

I am trying to organise some data which is not in a fixed format, and can only do this by finding out what position/s spaces are to determine the end/start of fields, what does Access offer to do this and how please?

 

[PaulBricker]

You don't give us much to go on but the InStr() function will locate the position(s) of spaces. The syntax would look like this.
Instr(1,"Hello World"," &quot

This expression would return 6 as the location of a space. You can use the function along with a loop to locate all the spaces in a string.

Paul

 

[SimoK]

Thanks Paul, think I can get it from this, what I am trying to do is to create a database from a pdf file, and while 99% of the fields line up every so often it throws up a line out of sync. If there are better ways to do it than look for spaces, don't hesitate to let me know

 

[Norris68]

If using a recent version of Access you can use the Split() function. This will take a string, split it on each occurence of a particular string (in your case a space) and return an array of strings of each 'word'. Use the UBound() function to find out how many were returned.

 

[SimoK]

Thanks Norris, trying the split function, with
try1: Split([Field1]," ",-1,-1)
and get
undefined function split in expression,
but Access XP finds split when I do a help from the function box. Any clues?

 

[PaulBricker]

I think what you need to do is put it in a function and call it from there.

Function mySplit(myField as String) as String
mySplit = Split(myField," ",-1,-1)
End Function

then in the query use
try1:mySplit([Field1])

Paul

 

[Norris68]

Where are you trying to put it? The Split() function is a part of VBA.

Try pasting the following in a module, then running:

Function SplitTest()
Dim Result() As String
Dim A As Long
Dim Response As String
Result() = Split("This is a series of words", " ")
For A = UBound(Result()) To 0 Step -1
    Response = Response & Result(A) & vbCr
Next A
MsgBox Response
End Function

The Split function splits the sentence into individual words. We then loop backwards through the results and display in a messagebox giving the result:

words
of
series
a
is
This

If it doesn't work then you possibly have reference problems, but I suspect you are trying to use it in a bound field. You need to return the results to a string array.