Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Alpha Numeric sorting

Posted on 2007-11-25
10
Medium Priority
?
1,288 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public 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 2000 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

618 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