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
Solved

Alpha Numeric sorting

Posted on 2007-11-25
10
1,284 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

791 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