SQL query sorted by last name when all I have is full name?

  • Thread starter Thread starter Noozer
  • Start date Start date
N

Noozer

My table has a column called "USER" that is the full name of that user. I
need to be able to sort the rows by the last name found in this column.

Is there a way to specify ORDER BY (Mid(User,InstrRev(User," ")+1)) ???

In English:

ORDER BY part of the USERs name starting after the last space found in the
name up to the end of the name.
 
Noozer said:
My table has a column called "USER" that is the full name of that user. I
need to be able to sort the rows by the last name found in this column.

Is there a way to specify ORDER BY (Mid(User,InstrRev(User," ")+1)) ???

In English:

ORDER BY part of the USERs name starting after the last space found in the
name up to the end of the name.
Click to expand...

Decided to fire up my other machine... The code above works fine.

Seems that the easiest way to make my code work is to ask if it's good
BEFORE I try it!

: )
 
....and now that I've coded this into my ASP page I find that the MS Jet
engine doesn't support the InstrRev function...

SELECT * FROM Entries ORDER BY (Mid(User,InstrRev(User," ")+1))

Help?
 
InStrRev is a VBA function, not JET's.

When you are accessing data in a JET MDB which is what you are doing, you
don't have access to the VBA functions.

I don't know ASP but my guess is that you have to retrieve the data and sort
the data in your ASP code.
 
If at all possible, I'd recommend changing the table to have separate fields
for the parts of the name. You could then use a query using InStrRev to
parse the existing data *within Access* before trying to use it in your ASP
app. It will need some tidying up afterward, as there are people with spaces
within their last name, but at least the query will do the bulk of the work
for you, and then new data can be entered into the new, separate fields,
eliminating the problem for the future.

If that's not possible, then as Van indicates elsewhere in this thread
you'll have to retrieve the data 'as is' and do the parsing and sorting in
your ASP code.
 
Back
Top