Alpha Numeric sorting

Dear Experts,
I have field like this in my access databse.
A/2000/01
A/2001/02
A/2002/03
A/2003/04
I am using this in the "SlNo" field  "A" & "/" & Format(DatePart("yyyy",Date()),"0000") & "/"
I have almost 1000 records, Please help me to sort it by year and last digits.

Regards
Aby
Aby2004Asked:
Who is Participating?
 
spgprogrammingConnect With a Mentor Commented:
If you have almost 1000 records I imagine that it's possible that the last 2 digits could go past 99 so you would have to sort on year first and then the last digits. First the year column:
SINSort1: clng(Mid([SinNo];3;4))
Ascending

next column:
SinSort2: clng(Mid([SinNo];8))
Ascending

I don't know how clear my answer is but I'm sure it works!~)
0
 
Barry CunneyCommented:
Create a new query with field from the table
At the beginning of all the fields add an expression field as folllows:

SINSort: CInt(Mid([SinNo],3,4) & Right([SinNo],2))

In the Sort row of the query grid specifiy ASCENDING or DESCENDING for this SINSort field
0
 
Aby2004Author Commented:
Not working. can u pls explain?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I highly suggest BCUNNEY's approach.  Often with time series data I'll use the YYYYMM as an Access Long Integer / SQL Integer value, and perform all sorting/grouping/math by that value, instead of using an expression on the date (or in this case, pseudo-date) value.

Also, define 'not working', as the above code looks fine, assuming that it's put in a query, and there is always one character before the first /, and a four-digit year, and a two-digit month.
0
 
Rey Obrero (Capricorn1)Commented:
try this

select val(mid(replace([sino],"/",""),2)) as SortFld, *
from NameofTable
order by val(mid(replace([sino],"/",""),2))
0
 
GRayLCommented:
Aby2004:  Time to hear from you.  Your examples will sort in that order by using just - SORT BY SlNo
0
 
Aby2004Author Commented:
Sorry guys, all the answers are excellent. As a biginer this is more easy and I found it is working. Others are also working. Thanks a lot for the assistance.
Regards
0
 
Aby2004Author Commented:
Sorry guys, All the answers are excellent. I found the accepted one is more easy, that is why I am giving the point to him. All the answers I tried and all are working fine. Thanks a lot for the assistance.
0
 
Aby2004Author Commented:
Hi spgprogramming,
I have another query related to the same question. When I link two tables with the same sorting field it is giving the following error.

"The specified field [SlNo] could refer to more than one table listed in the FROM clause of your SQL statement."
So how can I solve this problem?
0
 
spgprogrammingCommented:
If you had a table1 and table2 then in this field:
SINSort1: clng(Mid([SinNo];3;4))
You would write:
SINSort1: clng(Mid(Table1.[SinNo];3;4)) well.. or table2!~)
This syntax works allround...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.