Record Sorting

[tekyge]

If there way to sort a record by last name? Like the example below would sort by first is there a way to do it by last name?

example
ORDER BY [Projects].[ProjectName] ASC

 

[tekyge]

Oh I forgot! Sort by last name without puting the last name first.

 

[tekyge]

Got it here it is for all that need it

ORDER BY Right([Projects].[ProjectName],InStr([Projects].[ProjectName],' ')-1) ASC

 

[tekyge]

Scatch that the above code does not work right and I can't seem to get it to and help?

 

[johnwm]

Split your data into FirstName and LastName. You can do this once (redefine your table) or on the fly, using a temp table

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[tekyge]

Spliting the table is not an option. It has to be done with code. Thanks for your response.

 

[DotNetGnat]

can you restate your question with little bit more clarity...

-DNG

 

[tekyge]

Hi Dot. What I am trying to do now is sort records by last name but have it not put the last name first if that makes since

sql = "SELECT * FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("cs_1")) & " ORDER BY Right([Projects].[ProjectName],InStr([Projects].[ProjectName],' ')-1) ASC"

The above code doesn't work right, and I am not sure this can be done. ?

 

[DotNetGnat]

i dont see any field as last name...can you show us some sample data and the kind of result you are looking for..

i still dont understand what you meant by

"sort records by last name but have it not put the last name first"...

can you explain with a simple example of what you are trying to do...

-DNG

 

[unclerico]

Can you do this in a stored procedure??

 

[aumstu]

Is the ProjectName concatenated...so you need to split the name up before you can sort it by last name?

 

[tekyge]

Thanks guys, yeah I need to split it before I sort. The code below would be how to do it and would work only when you have a middle name or John & Jane Doe kinda name it doesn't work right. So I would sort by last name without reversing.

sql = "SELECT LastPaymentDate, Paid, Total, Mid([ProjectName],InStr([ProjectName],' ')+1) & " & _
"', ' & Left([ProjectName],InStr([ProjectName],' ')-1) as LstFrst FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("cs_1"))& _
" ORDER BY 4 ASC

 

[johnwm]

You're probably looking for the InstRev function. Look at:

http://www.w3schools.com/vbscript/func_instrrev.asp

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[AntiEarnie]

This sounds more like an sql thing from what you are saying. With no idea on the table structure am unable to guess at what the query should say but try replacing the "select *" with the column order from left to right. The "ORDER BY" is what actually governs the order the rows come back in. You probably already know that but stranger things have happend.

If you can't mess with the sql try just sorting the recordset using the .Sort method. It is fairly straightforward. And you can dump the recordset into whatever in pretty much any column order.

 

[DotNetGnat]

without some sample data and the kind of results you are looking for, its difficult to understand and suggest a query...

-DNG

 

[tekyge]

Gives error it appears it is not supported ny Jet


Microsoft JET Database Engine error '80040e14'

Undefined function 'InStrRev' in expression.

 

[AntiEarnie]

Huh, with no sql I am just guessing but from your post a little bit ago and the error you look to be mixing VB and SQL together. MS DB's might be able to hindle it though, not sure about them. If InStr/InStrRev is not valid SQL for your DB then can you just move it to the stage where you display/manipulate your recordset?

 

[tekyge]

The closes I can come to getting it to work is this.

ORDER BY Right([Projects].[ProjectName],InStr([Projects].[ProjectName],' ')+0)

 

[AntiEarnie]

Hmm, well this works in access at least:

SELECT ProjectName 
FROM Table1
order by Right(ProjectName, len(ProjectName)-InStr(ProjectName, ' '));

Is this along the lines of what you are trying to do?

 

[tekyge]

Yeah it does work great, but the only problem is that if you have a second name say John & Jane Doe then its going to sort at Jane rather than Doe and I need it to always sort at the last name regardless. Thanks much for all your help.