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

 

[Tarwn]

Hmm, I don't have an MS database handy right now, but off the top of my head couldn't you do something like:

ORDER BY RIGHT(ProjectName,CHARINDEX(REVERSE(ProjectName),' ') - 1)

I may be mixing syntax with something else, but basically the aim is to find the last space in the string, then use that position as the length to grab off the end of the original string.

-T

 

[tekyge]

Gives this error

Microsoft JET Database Engine error '80040e14'

Undefined function 'CHARINDEX' in expression.

 

[DotNetGnat]

i dont think CHARINDEX works in Access database...

as i told you tekyge...post some sample data and the result you are looking for...

-DNG

 

[tekyge]

Belopw is the sql code I use to show a report the report loops via a function in the page that shows the results of the code below. All I need is for it to sort those results via lastname but not put the last name first Tarwn is right I am try to find the last space in the name as a starting point. If I could do want Tarwn was tring to do it would be perfect.

dim conn, rs, sql, intTelephone, intMobile
set rs = Server.CreateObject ("ADODB.Recordset")
Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Data\Data.mdb;" & _
"Jet OLEDB:System Database=C:\Data\Scur.mdw", _
"xxx", "xxxxxxxx"

sql = "SELECT * FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("cs_1")) & " order by Mid(ProjectName, Len(ProjectName)-InStr(ProjectName, ' ')) ASC

 

[Tarwn]

Hmm, not sure if Reverse is supported either, sorry, was too lazy to go get the work laptop out of the car and wasn't sure whether you were using SQL Server or Access. Granted my above example wouldn't work out of th bx with either

Ok, so for access you don't have a CHARINDEX or REVERSE, but what you do have is InStrRev. Basically works the same way as inStr except it starts at the end of the string and works towards the front, so the following should work:

ORDER BY Right(ProjectName,Len(ProjectName) - InStrRev(ProjectName,' '))

Same basic concept and also points out an error in my original, in that I wasn't subtracting from the length of the string (long day yesterday).

Hope that helps,
-T

 

[tekyge]

Thanks Tarwn for your help. It is now giving this error

Undefined function 'InStrRev' in expression.

 

[Tarwn]

Ok, I did some looking around and I have managed to affirm my belief that MS Access really sucks.
Apparently I was remembering correctly that InStrRev is supported by Access, however it is not supported in Jet SQL statements. The only workarounds I can think of are pretty ugly, but you may not have a choice.
Your two options are to either sort your data in your ASP code after making your query, or assume you will only have a certain maximum number of spaces in your field and use a series of IIF and InStr statements.
Here is an example of the IIF/InStr approach that I found while researching your problem:

http://support.microsoft.com/?kbid=209045

And if your interested in the sorting method you should be able to find some stuff that has been posted in the past by using the search above. There may be something in the FAQs as well.

Sorry if I got your hopes up, I was aware that Jet SQL queries were limited, but had assumed they would at least have some equivalent to Reverse or InStrRev.

-T

 

[tekyge]

No that’s ok I have read up on the same thing about this and do greatly thank you for your help and all that tried.

I think I am going to just forget it since I do have search capability for clients and if they need to search by last name they can. I also am going to begin building an SQL database so that in the next year or so we can upgrade to SQL rather than access as it is my believe this would in the long run be a more secure and priceless endeavor.

Thanks ALL