OK - here's the situation:

I have a table with a field, dealnumber, that could contain the following values (as examples):

ATP-SP
ATP-SP2
ATP-SP19
BP-SP
BP-SP2
BP-SP19

I would like to select and group the data by the portion of the deal number that goes up to "SP" (i.e. ATP-SP, BP-SP). I initially attacked this by using LEFT() and AT() as follows:

SELECT LEFT(DealNumber, AT("-SP", DealNumber)+2) AS TrimmedDeal

That query only returns the first two characters of the deal number (i.e. "AT", "BP"). Yet, when I do the following query:

SELECT AT("-SP", DealNumber) AS TrimmedDeal

I do in fact get back the right number that could be used as the second parameter to the LEFT() function.

My questions are:
1. Why doesn't AT() work inside a LEFT() within a SELECT statement?
2. Since it obviously doesn't work, how can I accomplish what I'm after?

Laterness,
Jon