Help with InstrRev

[BirdDogg]

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.

and here is the SQL

SELECT Workorder.OrderNo, 
Workorder.OrderLine, 
Left(Workorder.DrawingDesc,InStrRev(Workorder.DrawingDesc,"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

 

[EMP]

Try these for SEG1, SEG2 and Partname.

IIF(InStr([DrawingDesc],"CNC"), Trim(Left([DrawingDesc],InStr([DrawingDesc],"CNC")-1)), Null) AS SEG1,
IIF(Instr([DrawingDesc],"CNC"), Trim(Mid([DrawingDesc],InStr([DrawingDesc],"CNC")+3)), Null) AS SEG2,
IIf(InStr([SEG2],"#"), Replace([SEG2],"#",""), [SEG2]) AS Partname

^

 

[BirdDogg]

Thank You! It works