Query - sort by "part" of the column value

[ConnieL1954]

Hi, I use the software "Parish Soft" and their reports and queries are written but I am able to edit them. The address field contains both house number and street name and I want to sort by the street name only. I don't want to change the structure of the table but Is there a function that I can use to sort by the fifth value in the address column using SQL? I have used other programming languages but I'm not that familar with access and can't find the syntex. Thanks for any help. ConnieL1954

 

[pbaldy]

I'll be very surprised if it's always the fifth value, but you can use the Mid() function in a new field and sort on that. You can also use the InStr() function to find the first space and use that in the Mid() function.

 

[plog]

In Access you can extract a substring using the Mid function (http://www.techonthenet.com/access/functions/string/mid.php). So you should bring your table into a query, create a new field like this:

SortField: Mid([YourFieldNameHere], 5,1)

Change YourFieldNameHere to the correct field and that will put the fifth character of it into SortField (i.e. 123 Main Street --->> M). You could also use this:

SortField: Mid([YourFieldNameHere], 5)

To get all the characters after the fifth string (i.e. 123 Main Street --->> Main Street)