i am currently doing a project in access which involves importing large amounts of text data. the data is in the format-

module code followed by module title followed by semester followed by credits
the module code is always 6 alphanumeric charechters, the module title varies for different modules - it is completely inconsistent and may be 1 word or upto ten words, the semester is either 'A' 'S' or 'B', and the credits are between 0-120 in multiples of 5.
the problem with the data is that there are no seperators, the data is seperated by spaces, however the module title may also contain spaces within it.
it is easy enough to seperate the module title from the other details, however after this i find it very diifficult to seperate the remaining module details.
Our professor has suggested using calculated fields with an expression builder

iff(mid(details),len(details)-1,1)=" ",1,iif(mid(details),len(details)-2,1)=" ",2,3)

where details is the field containing the module details
Now what i understand from this expression is it will calculate the length of the field and minus one from it, if the charechter in this position is a [space] it will be identified by a 1, this will occur in the case of single digit credit modules (ie 0,5 credit modules)
if the charechter is not a space it will go further into the string and if the next digit from the right is a space it will return the value 2,this is for all the two digit credit modules. and finally it will return a three for all other results ( ie the three digit credit modules). in this way we will have numeric identifiers for the last space, and therefore ccess will be able to use the last space to sort the data, all data after the last space is credits, and the data immediatley preceeding the last space is semester.
once we hae the position of the last space in numeric terms it is possible to extract the data using the right and id operators.
however just trying to set up the expression i consistently come up with syntax errors or access simply crashes. could you please tell me how to proceed with this . if you like i can mail you the text file containing the data.
in desperate need of help.thanx a lot
avik

It would be helpful if you'd keep all your questions regarding a particular problem in one thread.

I previously provided, in another of your threads, the xLastInStr() function to find the last occurrence of any character or string of characters.

Then came upon your original thread describing the entire problem. Here's an example of how to break out the various components, based on your description:

From the debug window:

'the original string
widget = "abc123 The quick brown fox jumped over the brown lazy dog S 120"

'the code is the leftmost characters preceding the first space
myCode = left(widget, instr(widget, " ")-1)

'the credits are the right characters following the last space
myCredits = mid(widget, xlastinstr(widget, " ")+1)

'the semester is a single character preceding the last space
mySemester = mid(widget, xlastinstr(widget, " ")-1, 1)

'the length (of the title)-end point of the title is three characters preceding the last space
'the startpoint
myLen = xlastinstr(widget, " ")-3 - instr(widget, " ")+1

MyTitle = mid(widget, instr(widget, " ")+1, myLen)


? mycode
abc123
? mytitle
The quick brown fox jumped over the brown lazy dog
? mysemester
S
? mycredits
120


'************************************************* ***
Function xLastInStr(ByVal tstr As String, twhat As String) As Integer
'************************************************* ***
'Name: xLastInStr (Function)
'Purpose: Return location of last instance of a character or phrase.
'Author: raskew
'Inputs: Call xLastInStr("the quick brown fox jumped the lazy dog", "the")
'Output: 28 - Location of last occurence of "the"
'*******************************************

Dim i As Integer, n As Integer, tlen As Integer

n = 0
tlen = Len(twhat)
For i = Len(RTrim(tstr)) To 1 Step -1

If Mid(tstr, i, tlen) = twhat Then
n = i
Exit For
End If
Next i

xLastInStr = n

End Function