parse a field 2

[chris100]

I need help moving part of one field to another field.

the Field Name is "Description".
This field holds both a description and a number.
Example: (ASLT Measurement (C0102992)

I need to move the(C0102292)into a field of its own.

Where this number appears is never the same because the description varies in length.

Thanks in advance for the help.

Chris

 

[chris153]

Hi,

Does it always start with C and is there ever a C elsewhere in the description

Also, is the (C0102292) always the same length and at the end of the description ?

PS, nice name !!!

 

[dhookom]

You can extract the value using the expression:
Mid([Description],Instr([Description], "(C")
As per chris153's post, this may vary if there might be (C anywhere except at the end of the description field value.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[chris100]

Thanks for the quick response.

Chris153 - Yes the code number always starts with a C. It is also has both left and right parens around it - but is not always the same length.

Yeah, it is a great name!

Thanks.

 

[chris153]

Its abit nasty but effective (like herpies cream!)

Function Hello(Chris100 As String) As String

Hello = Mid(Chris100, InStr(1, Chris100, "(C", vbTextCompare) + 1, InStr(1, Chris100, ")", vbTextCompare) - InStr(1, Chris100, "(C", vbTextCompare) - 1)

End Function

You have to reference the function and pass the string as Chris100 in the query