Using InStr() inside Mid()

[urbi]

Hello!!
I have a field "Course" with data like:
AAA 210
AAA 210A
AAAA 300
AAAA 300A
AA 250
AA 250A
AAAAA 300
AAAAA 300X

I am doing:
Mid([tablename]![Course],InStr([tablename]![Course]," ")+1,3) to get just 3 numbers after the space.

what I am getting:
AAA 210-->2
AAA 210A-->2
AAAA 300-->30
AAAA 300A-->30
AA 250-->blank
AA 250A-->blank
AAAAA 300-->300
AAAAA 300X-->300

This is not giving me those three numbers..Please help!!!

Thanks in advance

 

[MarkK]

Check out the Split() function, which returns an array of strings with " " (space) as the default delimiter...

Left(Split("AAAA 300A")(1), 3)

 

[pbaldy]

Cross posted. Problem is multiple spaces in the middle.

 

[MSAccessRookie]

I am not quite sure what your issues is. If you can elaborate we might be able to find where you went wrong. I did the following:

1. Created a Blank Database
2. Created a new Table named tablename with one Field named Course
3. Entered all eight example data items into the Table
4. Created a new Query in Design view that used the Table tablename
5. Created one Field to select defined as

Expr1: Mid([tablename]![Course],InStr([tablename]![Course]," ")+1,3)

When I did this, the results were exactly what you said you were looking for, only the three digit numbers.