Re: ARGH! SQL query sorted by last name when all I have is full name?
- From: "Brendan Reynolds" <brenreyn@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 4 Aug 2005 13:48:50 +0100
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.
--
Brendan Reynolds (MVP)
"Noozer" <dont.spam@xxxxxxx> wrote in message
news:Qu%He.104128$s54.30698@xxxxxxxxxxx
> ...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?
>
>
> "Noozer" <dont.spam@xxxxxxx> wrote in message
> news:fi%He.100843$5V4.32441@xxxxxxxxxxx
>>
>> "Noozer" <dont.spam@xxxxxxx> wrote in message
>> news:Xc%He.104112$s54.23046@xxxxxxxxxxx
>> > 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.
>>
>> 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!
>>
>> : )
>>
>>
>>
>
>
.
- References:
- Prev by Date: Re: Time differences - Query
- Next by Date: Re: Time differences - Query
- Previous by thread: Re: ARGH! SQL query sorted by last name when all I have is full name?
- Next by thread: simple criteria question
- Index(es):
Relevant Pages
|
