Seperate a imported field from Outlook

[richard]

Hi

I have imported data from Outlook. The field I am interested in is the
'Subject' Field. This contains 3 effective segments,
1 employee name
2 description
3 job and sub job number

example of text in field is

M.Heywood Completed Job No.1708 8
N.Curry Completed Job No.1477

I wish to move the employee name into another field and also the job and sub
job number. I have tried the following Right() Function but it is not quite
working as I want

Right$([Subject],InStr([Subject],"Completed Job No.")-1)

For the records above the results are as follows

No.1708 8
No.1477

What I really want is to only get the text after the .

Any help please as this is my first time with this function

Thanks

Richard

 

[Brendan Reynolds]

Hi

I have imported data from Outlook. The field I am interested in is the
'Subject' Field. This contains 3 effective segments,
1 employee name
2 description
3 job and sub job number

example of text in field is

M.Heywood Completed Job No.1708 8
N.Curry Completed Job No.1477

I wish to move the employee name into another field and also the job and
sub
job number. I have tried the following Right() Function but it is not
quite
working as I want

Right$([Subject],InStr([Subject],"Completed Job No.")-1)

For the records above the results are as follows

No.1708 8
No.1477

What I really want is to only get the text after the .

Any help please as this is my first time with this function

Thanks

Richard

This will get you the job number (substitute your own table and field names
of course) ...

SELECT tblTest.TestText, Mid$([TestText],InStr(1,[TestText],"Completed Job
No.")+17) AS JobNo
FROM tblTest;

The InStr() function will return the starting position of the searched-for
text, so we need to add on the length of the searched-for text, in this case
17, the length of "Completed Job No.".

You could re-write the above as ...

SELECT tblTest.TestText, Mid$([TestText],InStr(1,[TestText],"Completed Job
No.")+Len("Completed Job No.")) AS JobNo
FROM tblTest;

This would be slightly less efficient (but I believe only slightly, as I
believe JET would only needed to evaluate the Len() function once) but would
have the advantage of being more easily understood, as in the first example
it is not immediately clear what the significance of the number 17 is.