Extract Specific Data

[CJ]

Hi Groupies

The data in my Job Number field looks like this:
DC-2010-1-63 - City Hazard
AB-2010-5-960 - Shop

How can I extract everything up to the 4th dash so that I am left with:
DC-2010-1-63
AB-2010-5-960

Thanks in advance.

 

[Marshall Barton]

The data in my Job Number field looks like this:
DC-2010-1-63 - City Hazard
AB-2010-5-960 - Shop

How can I extract everything up to the 4th dash so that I am left with:
DC-2010-1-63
AB-2010-5-960

Assuming the 4th dash is always preceeded by a space.

If the 4th dash is guaranteed to be the last dash:
Mid(jobnum, 1, InstrRev(jobnum, "-") - 2)

Otherwise:
Mid(jobnum,1,InStr(InStr(InStr(InStr(1,jobnum,"-")+1)+1)+1)-2)

 

[Jerry Whittle]

Assuming that the first space always occurs just before the 4th dash,
something like this in a query with the proper field name:

TheJN: Left([Job Number],Instr([Job Number], " ")-1)

 

[CJ]

Thanks Marsh!

I have never used InstrRev or InStr before. Would have taken me all summer!