Copy and change text into another field

[Alex Martinez]

Hello,

I work for an insurance company and I am wondering if I can copy some of the
text in one field to another field: For example in the text field I have
"Called Policy # X88889 for review" What I want to do is to copy the X88889
only into another text field. I know I will need to do this in a query, but
how. Thank you in advance.

 

[John Vinson]

Hello,

I work for an insurance company and I am wondering if I can copy some of the
text in one field to another field: For example in the text field I have
"Called Policy # X88889 for review" What I want to do is to copy the X88889
only into another text field. I know I will need to do this in a query, but
how. Thank you in advance.

If - and I fear it's a big and ugly IF - you can count on the code
number ALWAYS being preceded by an octothorpe # and a blank, and being
six characters long, you can use an expression

PolicyNo: Mid([fieldname], InStr([fieldname], "# ") + 2, 6)

This is an excellent example of why fields should be atomic, i.e. not
contain other fields!

John W. Vinson[MVP]