Sort numerical data from alpha numerical data

[prodtest]

Hi
I have a query which shows customer orders and purchase orders. The data is as below:

Order no Customer Date Item
-------- -------- ---- ----
4056 Fred 01/08/2004 68709
4059 Paul 05/04/2003 68701
PO050879 TIS 07/01/2005 68711
4025 James 03/02/2005 68705
PO005205 MPE 07/05/2005 68715
etc

I need to be able to seperate the 4 digit customer order numbers from the PO****** alpha numerical numbers.
Is there something I can put in the criteria field which seperates this out, I did try >4000 but it still shows the PO numbers.
Any Ideas ???

Cheers
Ben

 

[PHV]

Sort numerical data from alpha numerical data
ORDER BY Val(Replace([Order no],'PO',''))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[prodtest]

I have just relised how to do exactly what you just wrote !! Was having a major mind blank this morning !!
Thank you