By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 988 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Sorting in a query...

P: 1

We use Access 97 and I am trying to do something in a query but need some help.
Hopefully someone here will be able to do this for me.

I have a query that is picking up a field that I need to sort by.
The field is called IntRef and the data is in the format of xxx/yyy.
Both xxx and yyy can be 1, 2 or 3 digits.
This is a text field, (I can't change that as it is coming from a linked table).
Obviously, the order I get them sorting in is: 1/13, 1/4, 212/300, 4/1 etc...
I need these in the order of 1/4, 1/13, 4/1, 212/300.

The idea I have is, (and please feel free to tell me this won't work...):

Create another field in the query that is hidden and use that to sort.
This other field would read the IntRef field and alter it with leading zero's so that both sets of digits are three characters long.

Is this possible?
Can anyone show me the expression to use to get it to do this?

All help will be gratefully received...

Jan 9 '07 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 15k+
P: 31,494
Try :
Expand|Select|Wrap|Line Numbers
  1. ORDER BY Format(Left(IntRef,InStr(IntRef,'/')-1)*1000+Mid(IntRef,InStr(IntRef,'/')+1),'000000')
-Adrian :)
Jan 9 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.