Query Syntax 2

[DirtyB]

I need some help with this query. I have a field that has a series of numbers in it. Each series has a space in the field somewhere. I need to pull out the second number after the space. Unfortunately, the numbers are all representative of something, but do not necessarily have to appear. So, sometimes there could be two spaces before the number I need. For example:

2000 610 (I need the 1)
2000 110 (I need the second 1)
2011 610 (I need the 1)
10000 031 (I need the 3)
4000 39 (I need the 3)
4013 11 (I need the second 1)

The file was a fixed width file that was damaged, and several of the fields got grouped together into one field. So I can't say, I need the "second from the right", because there may not be another character on the right that I need. So, basically, I need to know how to select a certain position in the field, whether it exists or not. Does anyone know how to do this in Access?


Thanks

 

[tlbroadbent]

This should do what you need.

SELECT
Mid(Trim(Mid([field1],InStr([field1]," &quot)),2,1) AS NewVal
FROM Table;
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[DirtyB]

That worked PERFECTLY....Thank you so much