How do you sort text field which contains numbers in Access

[perrydaniel]

I have a table where one of the fields is a reference number, the reference number starts with a letter followed by a number.Example A'1, A'2, A'3...A'11, B'1, B'2 etc. At present when sorting by this field I get the following list.
A'1
A'11
A'2
A'3
B'1
B'2

How would i go about making the list sort by letter first, then the number. As follows.

A'1
A'2
A'3
A'11
B'1
B'2

Would appreciate any feedback. Thanks Perry

 

[KenReay]

Hi

If Format is a regular as you say then:

ORDER BY Left(MyCol,1), Format(Mid(MyCol,2),"000")

should do it

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[perrydaniel]

Hi Ken

Thanks for your reply, I have tried your suggestion, however i have replaced mycol with reference, but i get a compile error on left(reference,1)?

Thanks

Perry

 

[KenReay]

Hi

Compile Error?, I thought this was an SQl string?

Please confirm it is an SQL string and post the string here, if it is VBA code post the code

There is nothing fundamentally wrong with Left(stringvariable,length)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[perrydaniel]

Ken

Yes it is SQl.

SELECT *
FROM TBook
order by left(reference,1), format(mid(reference,2),"000") ;

Perry

 

[KenReay]

Hi

Try

SELECT *
FROM TBook
order by left([reference],1), format(mid([reference],2),"000") ;


if that does not work

Are you doing this in the query designer?

What does the error message actually say?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[perrydaniel]

Ken

Compile error. in query expression'left([reference],1)'.

I am writing query in SQL, my table has a number of fields, the fiels that I wish to sort the data by, is called reference. If it makes any difference this is the 2nd column of my table.

Thanks

Perry

 

[KenReay]

Hi

OK, I suspect you have a missing reference, would you try using the left function in the immediate window, just something simple like

? Left("ABC",2)

see if it works

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[perrydaniel]

Ken,

Thanks for your patience, but I have no experiance of VB in anyway? Where do I start?

Thanks


Perry

 

[KenReay]

Hi

In the db window, go to modules, click new, choose view \ Immediate Window

an empty window will appear, type as I said ? Left("ABC",2)

tell me the result

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[perrydaniel]

Ken,

I am having no luck with this one, looking back at what you said yesterday, if I remove the ' from A'1,to show A1 the first solution seems to work. I dont think this will cause me any problems. Will it make any difference if I have a ref Mc11, will this cause a new problem?

Thanks

Perry

 

[KenReay]

Hi

In my first post I said "If Format is a regular as you say then:"

The format is not as regular as you say, ie sometimes you have two alphas followed by numbers, sometimes one

The only way I can thing to handle this is via a user function.

By the way, I thought the ' in your example was a typo, if it was not, it should have been:

ORDER BY Left(MyCol,2), Format(Mid(MyCol,3),"000")


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[perrydaniel]

Ken,

Thanks for your help, seems to be working great now.

Perry