Copy only part of text string

[hobbit2612 via AccessMonster.com]

Hi,

Wonder if someone maybe able to help me please.

I have a database at work that contains details of test files. One of the
fields for each test shows that actual error that caused the file to fail.
The problem is, is that there is an awful lot of text from this field that I
don't need to extract.

The text I need to extract starts with the word Error and then the rest of
the text that comes after it in the text string. The word Error can appear
anywhere within the text string, but having worked through all of the
failures I beleive that 'Error' can be used as the guide to start extracting
from.

I'm fairly new to Access, and I did try using the 'Mid' and 'Last'
functionaity, but this didn't seem to work.

Could someone perhaps point me in the direction please of how I could put a
function/coding into a query that will extract the information and where if
the word 'Error' doesn't appear within the text string it just leaves the
field blank in the query.

Many thanks an regards

 

[Michel Walsh]

InStr() could be used to spot the word Error:

? InStr("Hello, World, is that an error we just saw?"", "Error" )
26

So, you can then retrieve from that position (+5, if you don't want the word
error itself):


Mid( myString & "Error", 5+ InStr(myString & "Error", "Error") )


Note that I appended the searched string to the initial string, so it is
always 'found'. Indeed, if the string is not found, InStr returns 0 and Mid
would produce an error, in that case.




Hoping it may help,
Vanderghast, Access MVP

 

[hobbit2612 via AccessMonster.com]

Michael,

Thanks for this.

Like I said I'm relatively new to Access so could I just check my
understanding.

With the InStr formula, do I actually have to put the text in the query field
as you have shown in the bracket? it's just that the text will be different
because it relates to different errors so i won't be able to do this.

Sorry for being a bit thick!

Could you possibly expand?

Regards

Chris

InStr() could be used to spot the word Error:

? InStr("Hello, World, is that an error we just saw?"", "Error" )
26

So, you can then retrieve from that position (+5, if you don't want the word
error itself):

Mid( myString & "Error", 5+ InStr(myString & "Error", "Error") )

Note that I appended the searched string to the initial string, so it is
always 'found'. Indeed, if the string is not found, InStr returns 0 and Mid
would produce an error, in that case.

Hoping it may help,
Vanderghast, Access MVP

Hi,

[quoted text clipped - 24 lines]

Many thanks an regards

 

[Michel Walsh]

There is no need for the text to be a constant, it can be a field name.


ExprNNN: Mid( FieldName & "Error", 5+ InStr(FieldName & "Error", "Error") )


in the grid, or


SELECT Mid( FieldName & "Error", 5+ InStr(FieldName & "Error", "Error") )
AS ExprNNN
FROM tableName


in SQL view.


Vanderghast, Access MVP

Michael,

Thanks for this.

Like I said I'm relatively new to Access so could I just check my
understanding.

With the InStr formula, do I actually have to put the text in the query
field
as you have shown in the bracket? it's just that the text will be
different
because it relates to different errors so i won't be able to do this.

Sorry for being a bit thick!

Could you possibly expand?

Regards

Chris

InStr() could be used to spot the word Error:

? InStr("Hello, World, is that an error we just saw?"", "Error" )
26

So, you can then retrieve from that position (+5, if you don't want the
word
error itself):

Mid( myString & "Error", 5+ InStr(myString & "Error", "Error") )

Note that I appended the searched string to the initial string, so it is
always 'found'. Indeed, if the string is not found, InStr returns 0 and
Mid
would produce an error, in that case.

Hoping it may help,
Vanderghast, Access MVP

Hi,

[quoted text clipped - 24 lines]

Many thanks an regards

 

[hobbit2612 via AccessMonster.com]

Michael,

Yes I understand now. In fact I tried it at work this morning and it worked a
treat. Thanks very much, it's helped me out no end.

Appreciate your time and trouble.

Kind regards

Chris

There is no need for the text to be a constant, it can be a field name.

ExprNNN: Mid( FieldName & "Error", 5+ InStr(FieldName & "Error", "Error") )

in the grid, or

SELECT Mid( FieldName & "Error", 5+ InStr(FieldName & "Error", "Error") )
AS ExprNNN
FROM tableName

in SQL view.

Vanderghast, Access MVP

Michael,

[quoted text clipped - 41 lines]