Order by Defined Field? 1

[MikeRBS]

I have this simple one-table query:

SELECT People.ID,
People.CompName,
InStr(1,[CompName]," ") AS Sblnk,
Len([Compname]) AS CNlen,
Mid([CompName],([Sblnk]+1),([CNlen]-[Sblnk])) AS Surname,
Left([CompName],([Sblnk]-1)) AS FirstName,
People.Group1,
FROM People
;

It picks out surname and firstname from a "John Doe" type field. Trouble is if I try to ORDER BY Surname, Access throws up a parameter box asking for Sblnk. Does this mean you can't order on a field that is calculated?

 

[lespaul]

No, you can order by a calucation, but you can't use the alais in the order by, you have to restate the calculation (and unless you are using Sblnk and CNLen for something else, I would change the SQL):

SELECT People.ID,
 People.CompName,
 Mid([CompName],(InStr(1,[CompName]," ")+1),(Len([Compname])-InStr(1,[CompName]," "))) AS Surname,
 Left([CompName],(InStr(1,[CompName]," ")-1)) AS FirstName,
 People.Group1,
 FROM People
 ORDER BY Mid([CompName],(InStr(1,[CompName]," ")+1),(Len([Compname])-InStr(1,[CompName]," ")))

Leslie

 

[MikeRBS]

Thanks Leslie.
That just seems bizarre. Access has to do the same calculations 3 times, for no obvious reason.

 

[MikeRBS]

Leslie

I cut and pasted your SQL and it worked straight off - just to let you know your efforts are not wasted.

Mike

 

[lespaul]

Glad it worked!

leslie