Help With InstrRev

Access 2003

I need help separating a string into two parts. I have it working perfectly if the data is exactly as specified, though some records may not have the keyword "CNC" or it may be blank so I would like that record to either skip the InstrRev part of the query or insert a replacement value e.g. <no drawing>. I would also like to learn how to do a replace where I am removing the # and not leave a space in it's place.

Below is a sample of my query results with "normal" records indicated. Again, I need to deal with blank fields and those that do not have the "CNC" keyword.

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=19296&d=1193714440

and here is the SQL
SELECT Workorder.OrderNo,
Workorder.OrderLine,
Left(Workorder.DrawingDesc,InStrRev(Workorder.Draw ingDesc,"CNC")-1) AS SEG1,
Right(Workorder.DrawingDesc,
Len(Workorder.DrawingDesc)-InStrRev(Workorder.DrawingDesc,"CNC ")-3) AS SEG2,
Replace([SEG2],"#",'') AS Partname,
Workorder.DrawingDesc,
Workorder.QtyReq,
Workorder.DateRcvd,
Workorder.DateReq
FROM Workorder;

Any help greatly appreciated