Solved

Alpha Numeric sorting

Posted on 2007-11-25
10
1,280 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
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 65

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
 
LVL 119

Expert Comment

by:Rey Obrero
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now