SQL Order By Question

[PJB]

My sql statement is selecting a list of addresses how can i make it ignore the house number and order it alphabetically when the house number comes first when data is entered(ie 15 This Street)?

almost forgot, the sql statement i'm using now is

Select Address From Property Order By Address


[Edited by PJB on 10-16-2000 at 02:27 PM]

[HunterMcCray]

ORDER BY Trim(Mid(TableName.FieldName, Len(Str(Val(TableName.FieldName))) + 1, 0)

This should even work for "117B Oak Street".

Val("117B Oak Street)=117
Str(117) = "117"
Len("117")+1=4
Mid("117B Oak Street", 4, 0) = " Oak Street"
Trim(" Oak Street") = "Oak Street"

This is a backwards way of doing things, but it should work. If you use this type of sort frequently then you should consider creating two Fields: one for street adress and one for street number. You can use the same formula to change the data to the correct format from a user's text box and put the strings back together in code before you display them.

Hope it helps,

Hunter

[PJB]

i plugged that in and it's giving me a "wrong number of arguments" error

[HunterMcCray]

Originally posted by PJB
i plugged that in and it's giving me a "wrong number of arguments" error

Try This:

Dim txtOrderBy as String

txtOrderBy=Trim(Mid(TableName.FieldName, Len(Str(Val(TableName.FieldName))) + 1, 0)


txtSQL="......" & "ORDER BY " & txtOrderBy & ";"

Hunter

[PJB]

I'm thinking not being in order isn't all that bad, I tried that and it gave me the error "variable not defined" on the field name so i defined the field name as a variable and the only thing it's doing now is returning the data in reverse the order of entry, II'll show ya the mess i have and see if you can make anything of it


Dim temporder As String
Dim tempadd As String
tempadd = Data1.Recordset!Address
temporder = Trim(Mid(tempadd, Len(Str(Val(tempadd))) + 1, 0))
Data1.recordSource = "Select Address,City,State,ZipCode,NoOfUnits, PropertyID From Property ORDER BY ' & temporder & ' "
Data1.Refresh



don't see why the user is dead set on alphabetical order anyway when he insisted on a find function so he could find the address

[VorTechS]

Sorry, I was passing through and had to have a look at this one because it was quite a useful thing for me to know about.

It seemed to me that there is a simple solution to this problem and providing you are using an MS Access based database this outta help you with your problem.

Forgive me if I oversimplify the problem but is it true that you want to remove anything up to the first space in the address and use the latter portion as your order by clause?

If so then this seemed to work for me:

ORDER BY mid$(deliveryaddress, instr(deliveryaddress, chr(32)), len(deliveryaddress)- instr(deliveryaddress, chr(32)))

Simply replace 'deliveryaddress' with the name of your field and potentially - hey presto!

Hope this helps!!

[HunterMcCray]

Originally posted by PJB
I'm thinking not being in order isn't all that bad, I tried that and it gave me the error "variable not defined" on the field name so i defined the field name as a variable and the only thing it's doing now is returning the data in reverse the order of entry, II'll show ya the mess i have and see if you can make anything of it


Dim temporder As String
Dim tempadd As String
tempadd = Data1.Recordset!Address
temporder = Trim(Mid(tempadd, Len(Str(Val(tempadd))) + 1, 0))
Data1.recordSource = "Select Address,City,State,ZipCode,NoOfUnits, PropertyID From Property ORDER BY ' & temporder & ' "
Data1.Refresh



don't see why the user is dead set on alphabetical order anyway when he insisted on a find function so he could find the address

....ORDER BY Mid([AddressInfo]![LineOne],Len(Str(Val([AddressInfo]![LineOne])))+1,Len([AddressInfo]![LineOne])-Len(Str(Val([AddressInfo]![LineOne]))))

Where AddressInfo is the name of my table, LineOne is the Field Name that contains the street address. This works in Access 2000. The problem before was that I had the Mid Function operators wrong, sorry, I was doing it adhoc from memory. Note you will get some strange results if you have Addresses like 706-A and an error if your address field is blank. This is really a weird thing to try to do. If it is important the you should create a seperate field for Street Number and Street Name, it will save you a great deal of time in the long run.

Hunter

[VorTechS]

If you use the ORDER BY Clause I used, the '706-a' doesn't become a problem!

[PJB]

gonna use VorTechs "over-simplified" version I'll just have to make some note to the user not to enter spaces in the number anywhere(thanx VorTechs).
Thanx for all the help Hunter i did learn a few tricks even if i don't use them in this instance i know i'll be using them in the future

If you have time VorTechS would you mind braking that string down and explaining how it works so i have a better understanding?

[Edited by PJB on 10-17-2000 at 10:58 AM]

[HunterMcCray]

VorTechS,

Your Solution is certainly prettier than mine. I was trying to trap for leading spaces in the address, and I got carried away. I generally seem to do things the hard way. Thanks for the input!

Hunter

[VorTechS]

More than happy to explain the way the query works. I just hope it's readable....!

The query:

ORDER BY mid$(deliveryaddress, instr(deliveryaddress, chr(32)), len(deliveryaddress)- instr(deliveryaddress, chr(32)))

The attempted explanation:

1. the first part of the mid$, tells us to use the field 'deliveryaddress'

2. The second part 'instr(deliveryaddress, chr(32))' says start from the point where the ascii character 32 (for space) FIRST appears in the field delivery address.

3. The third part part 'len(deliveryaddress)- instr(deliveryaddress, chr(32))' says: returning the (x) number of characters where x = the length of the delivery address minus the character position of the space in the second part.

Let's make that a little more readable.

Using the pre-determined delivery address .. '117b Oak Street' the formula becomes .....

instr(deliveryaddress,chr(32)) = 5
len(deliveryaddress) = 15

mid$(deliveryaddress, 5, 15 - 5) which equates to:

return the string from character position 5 for the next 10 characters which will return:

'Oak Street'

*PHEW* If that still doesn't make sense check out the help file for INSTR, and MID$.

[PJB]

That make perfect sense thanks again!!

[PJB]

only problem i've found so far is if the user doesn't put a space in somewhere the whole thing goes splat, especially if you use it on a Form_Load event...

[VorTechS]

Well, I had a quick go at that this morning, and this should solve that problem to:

ORDER BY iif(instr(deliveryaddress,chr(32)) > 0, mid$(deliveryaddress, instr(deliveryaddress, chr(32)), len(deliveryaddress)- instr(deliveryaddress, chr(32))), deliveryaddress)

What's changed here is the use of iif() which a slightly more complex version of the IF statement.

What's being said here is that, if there is a chr(32) in the delivery address, use the formula as previosuly explained. If not, then just use the delivery address as-is.

In fact, I think that will also deal with blank entries!!

[PJB]

That worked GREAT!!! I was gonna do something stupid like some kind of text validation on the Address entry from the beginning, already have it so you can't leave that field blank, the only thing i could think is was "that's gonna look quite silly, a box popping up telling you to put a space in your address"
If the end user can't deal with those entries without a space in them then to bad, The guys getting on my nerves anyway, i got the code out of a database book for a find form that has a text box on it, when you start entering in the text box it starts bringing up matches for what you've entered as you go, he had 3 entries
1 A Street
2 B Street
3 C Street
he said it didn't work, he opened the form and entered B in the textbox and nothing came up...