There's no reliable way to sort under those conditions.

In what order would you want the following names sorted?

Mary Lou Retton
Ludwig von Beethoven
Walter de la Mare
Bono

A couple of points to consider.
As Douglas points out, the sorting would not be reliable.
I would recommend not sorting tables, but perform the sorting in you
queries, reports, or forms.
I would also recommend you not carry first and last name in the same field.  
A frequent question is "how do I parse out my first and last name from my
name field?"  The answer is, you can't do this with 100% accuracy.  There are
two many variable possibilites.  The same holds true for addresses.  It will
make your life much easier if you carry each part of a name separately.  To
put them together for display is very easy.  For a complete name you might
consider:
Prefix (Mr. Mrs., Dr. Sir, etc)
First Name
Middle Name
Last Name
Suffix (Esq, Jr, IV, etc)

Many thanks for your reply, this is an assignment request, can you tell me if
there is an unreliable way? Hypothetically speaking if all the names were
simple like Jack Russell.

I fully agree with Douglas and Dave about the difficulties with this. I *hope*
the instructor is just giving an object lesson in why names should be broken
down into components as Dave suggests!

That said... you can base the Report on a Query containing two calculated
fields:

FirstName: Left([name], InStr([name], " "))
LastName: Trim(Mid([name], InStr([name], " ")))

Use these fields in the Report's Sorting and Grouping dialog; that's the only
way to sort data in a Report, it will ignore the sort order of a Query and
there *is* no such thing as the sort order for a table.

            John W. Vinson [MVP]