Need String Func. to find first instance of " " and order by next char 2

[Dor100]

I need a function to use in a query which would be similar to the Mid function (but Mid won't work here). Instead of telling the function how many characters to count, I need it to simply find the first instance of a blank space " " and then I intend to order query output by the next character, like this:

ORDER BY Function(FirstName Last Name)

The goal is to be able to order by last name for a table that has only first name/last name combinations in one field instead of two.

 

[EastWind]

InStr(StrVariable, " ")

If StrVariable is a concatenation of First and Last Names, assuming the First Name has no spaces, then the above will return the character position of the space between the names.

EastWind

 

[ByteMyzer]

Try:
[tt]ORDER BY Mid([FirstName Last Name], InStr([FirstName Last Name], " ") + 1)[/tt]

 

[Dor100]

I want to thank both of you for two excellent answers. I knew it had to be there. ByteMyzer's syntax has worked superbly.