somekind of a trim issue?

[dcavaiani]

SELECT Left([cust],InStr(1,[cust],"-")-1) AS custx, Val(Right(Trim([cust]),Len(Trim([cust]))-InStr(1,[cust],"-")+1)) AS Job

Above works IF there is a blank in byte 1, but NOT if byte 1 of field to be split is filled!

custx Job....... Cust

w x y 1.......... w x y-1

w x y -1...... w x y-1

 

[sneuberg]

InStr(1,[cust],"-")+1) looks suspect. Suggest trying

InStr(1,Trim([cust]),"-")+1)

 

[dcavaiani]

I wish I could say YES ;-)

 

[sneuberg]

Well now that you thanked me for a bogus answer I guess I have to give you something. If the problem is the Job expression then the more simplified version seems to work ok. I think the trim was getting in your way.

SELECT Left([cust],InStr(1,[cust],"-")-1) AS custx, Val(Right([cust],Len([cust])-InStr(1,[cust],"-"))) AS Job

The results I was getting with these is shown in the attached screen shot.

 

[dcavaiani]

You have MADE my Day (and maybe I WILL be able to get some sleep tonight).

Spot on! Case Solved! Thank you MUCH!