Solved

Alpha Numeric sorting

Posted on 2007-11-25
10
1,286 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:Aby2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 20345113
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
 

Author Comment

by:Aby2004
ID: 20345131
Not working. can u pls explain?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 20345596
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20345614
try this

select val(mid(replace([sino],"/",""),2)) as SortFld, *
from NameofTable
order by val(mid(replace([sino],"/",""),2))
0
 
LVL 1

Accepted Solution

by:
spgprogramming earned 500 total points
ID: 20346577
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
 
LVL 44

Expert Comment

by:GRayL
ID: 20346712
Aby2004:  Time to hear from you.  Your examples will sort in that order by using just - SORT BY SlNo
0
 

Author Closing Comment

by:Aby2004
ID: 31410838
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
 

Author Comment

by:Aby2004
ID: 20348178
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
 

Author Comment

by:Aby2004
ID: 20356232
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
 
LVL 1

Expert Comment

by:spgprogramming
ID: 20356660
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

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question