query or sort by last two digits

[bcmcjeep]

I just learned how to sort by last two digits on the excel board. How can I do this in Access? I have a column of social security numbers and would like to be able to sort them by the last two digits (how our paper records are filed) so I don't have to export to excel to accomplish this. Any easy ideas?
Thanks!
blc

 

[ddeldeb]

I'm not %100 sure on this, till I give it a try tomorrow (near bedtime for me now), but in SQL code you can use the substring(#,#) function to parse out and use only the part of the string you want. I'm sure you can edit an access query to do this also. I think the code would look something like this
SELECT (substring(#,#) thename) WHERE whatever SORTBY whatever...
I'll try to look up the correct syntax for this and post some.
Hope this helps for now. Dave,,

 

[dk]

Hi,

It's very easy to do this:-

SELECT TableName.FieldName FROM TableName ORDER BY Right$([TableName]![FieldName],2);

Change TableName and FieldName to whatever you need.

 

[bcmcjeep]

Sorting by last two digits

Sorry, did I forget to mention I'm a little slow? I have never worked with SQL etc, which Is what I think you are trying to show me. Can you walk me through this step by step. I tried to figure out what you are explaining with the help of the built in help files. I tried the SQL help info, opened a new query, closed the show tables, selected query, sql specific, data definition and entered the information you had. I must be barking up the wrong tree. Can you explain it to me like the Access 3 year old that I apparently am? Thanks so much for trying to help me! :

 

[dk]

OK,

Try this:-

1. Click Insert, Query from the main menu. Choose Design View.
2. Add the table(s) you want by clicking the Show Table button (it should be the one in between the ! and the Σ buttons.
3. Once you've selected the tables you're ready to add the fields to your query. Drag and drop the fields onto the query grid.
4. Now, in the first blank column to the right of your screen type this:- SortField:Right$([TableName].[FieldName],2) YOU MUST replace TableName and FieldName to match your data.
5. Click in your newly created field and choose Ascending from the Sort drop-down box.
6. Finally, uncheck the Show checkbox.
7. Run your query.

Hope that works for you. Please post back to the board (not email) if you need further help

 

[bcmcjeep]

Thank you!!!!!!!!!!!!!!!!!!!!!!1

:D This worked Fantastic!!
Thank you so much!!!!!
You are awesome!!!!!!! :D :D :D