Isolating a PO number in a text field

[ChuckW]

Hi,

I have a text field that has both a PO Number and an invoice number. The
people who have been inputing this field start with PO and then a four digit
number. Somethings there is a space and other times there is not (PO4560 or
PO 4560). Some of our POs also have a - followed by a letter (PO 4560-A).
Whether or not the PO is four characters or six, the next part is a /
followed by the invoice number (PO 4560-A/Inv 3433). What I want to do is to
isolate the four or six digit number and exclude the PO and the / and
everything afte the /. So I want to return a query that only has 4560 or
4560-A. Can someone help me with this?

Thanks,

 

[John Spencer]

To get everything up to the slash /


Left([TextField],Instr(1,[TextField],"/")-1)

To get everything after the PO and remove a leading space (x is the
above expression)

Trim(Mid(X, 3))

Combined that becomes
Trim(Mid(Left([TextField],Instr(1,[TextField],"/")-1), 3))


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================