Selecting only part of a field

[AC5FF]

I have a query that needs to only return part of the data stored in one field. The data that the query pulls is formatted like this:

3652 #18

What I need to use is just the "18" out of this result.

Yes, I know that it would have been better to design the table using two fields for this data instead of one, but this table was created eons ago and set up in the format another program requires.

I know I can use 'right' or 'left' functions to isolate just the "18", but the data can very a lot; i.e. the first section can be 1, 2, 3 ... etc charicters long, and the same can be true with the numbers after the "#". Also, there are occasionally letters on either side. The letters after the "#" would be needed.

Is there anyway that I can choose data based just on everything to the right of a "#" sign?

I would also like the query to return a "NOT FOUND" result if there was no "#" in the field - instead of just returning a nul value.

Thanks!

 

[boblarson]

Create a new field like this:

NewNameHere:IIf(Instr(1,[OriginalFieldHere], "#") > 0, Mid([OriginalFieldHere], Instr(1,[OriginalFieldHere], "#") +1),"NOT FOUND")

 

[AC5FF]

Thanks Bob!
I'll give this a try and let you know how it works..