Copying and pasting ceratin sections of text

[ir26121973]

Hi,

Wonder if someone maybe able to help me please. This is fairly complicated to explain and I am really quite new to VB.

Some time ago I posted a query I had on how to pick specific error codes out of a whole string of text.

e.g. The error is Error 15.1 and it has occured.......

From the above I just wanted to pick out 'Error 15.1'

I was kindly given the code below to use which works a treat.

SELECT MyTable.MyField, Mid([MyField],InStr([MyField],"Error")+6,4) AS ErrorCode FROM MyTable;

However new errors have been added that are longer in length e.g. Error 153.1. Now I know that I can, and have changed the 6,4 element of the code to take these longer codes into account.

The problem I have now, is that because I have made these changes, for the shorter codes, it not only takes the Error Code but some of the text after it aswell.

Is there anyway please that the code can take into both the short and long codes.

The one common thing is that they all start with the word 'Error' followed by numerics, then text. I'm not sure whether the code could be designed to say to look for the word 'Error' and then take out what follows until it gets to the next letter, thus taking out the short and long error codes.

Many thanks

Chris

 

[VicRauch]

Within the code you were given, you know that you can change the 6,4 to 6,5 to get a longer error code. What you need is code to change the 4 to be whatever length the error code actually is. The following will do that. Replace the 4 with the following code and it should work.

InStr(InStr(MyField,"Error")+6, MyField, " ") - (InStr(MyField,"Error")+6)

HTH,

 

[ir26121973]

Vic,

Thanks for this, I really appreciate your help. The application I have is at work so I will test this tomorrow.

Sorry to sound a bit thick, but am I right in thinking that I use my existing code up to the number 6 then the put this in after it.

Many thanks once again

Chris

 

[VicRauch]

Exactly right. The code replaces the 4 which is the LENGTH of the error code. At first you knew it would always be a length of 4, but now it can be longer, therefore you need code that will look at the length of the error code itself, and choose that length.

 

[ir26121973]

Vic,

Great, thanks a lot.

I'll let you know how the testing goes!

Kind regards

Chris

 

[ir26121973]

Vic,

Just thought I'd let you know that the testing went great.

Thanks very much for your time and trouble.

Regards

Chris