• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 83
  • Last Modified:

Extract an alphanumeric string from a long text field in a MS Access query

Hello

Can someone please help me? I have a long text field which contains an alphanumeric reference which I'm trying to extract in my query and can't seem to get it right.

The string can appear anywhere within the field and begins with "Ref:MSG" and is followed by 8 numbers. These 8 numbers change for each record and there's usually characters or spaces either side of the string.

A couple of string examples are...
action the below request:^Note:  Ref:MSG24621668>^
CUSTOMER^COMMUNICATIONS RE: Ref:MSG61781623non-applicable

The result I am looking for would be...
Ref:MSG24621668
Ref:MSG61781623

If someone could help me with this it would be very much appreciated.

Thanks
darls15
0
darls15
Asked:
darls15
1 Solution
 
als315Commented:
If length of expected result is always 15 symbols, you can use this construction in your query:
Mid([YourField],InStr(1,[YourField],"Ref:MSG"),15)
0
 
darls15Author Commented:
Awesome, thank you so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now