In an Access 2000 form, I have an ID_No text field containing data that is 

currently sorted in the following manner:



TEST 1

TEST 10

TEST 2

TEST 21

TEST 22

etc.



I already have the following as an Event Procedure: 

Private Sub Form_Load()

Me.RecordSource = "select * from [tblName] where [ID_No] ALike 'TEST%' 

ORDER BY [ID_No] ASC"





I'd like to order the field to display 



TEST 1

TEST 2

TEST 3

etc.





Any thoughts on how I can get it to order the data in numeric order?

If your real data are as orderly as your sample data below, then you can use

the Mid$() function to extract the numeric portion of the field and sort on

that expression--see help on Mid$().



If you can't count on the number starting in column <whatever> then you've

got a tougher problem.  You could probably write a VBA function that would

find & isolate the numeric portion, but it would probably be a pain to write

& maintain.  



If it was me, I'd redefine the table structure so that the two components of

ID_No were stored in two separate fields & concatenate them as necessary for

display to users.  (I believe that having so-called 'atomic' field values

(ones that hold one and only one item of information) is one of the tenets

of good db design.)  You may still wind up having to write the VBA function,

but you'd only have to maintain it long enough to split ID_No.



Hope that helps,



-Roy



-----Original Message-----

From: B Coluccio [mailto:bcoluccio@m...]

Sent: Wednesday, August 15, 2001 12:32 PM

To: Access

Subject: [access] Ordering in 2000





In an Access 2000 form, I have an ID_No text field containing data that is 

currently sorted in the following manner:



TEST 1

TEST 10

TEST 2

TEST 21

TEST 22

etc.



I already have the following as an Event Procedure: 

Private Sub Form_Load()

Me.RecordSource = "select * from [tblName] where [ID_No] ALike 'TEST%' 

ORDER BY [ID_No] ASC"





I'd like to order the field to display 



TEST 1

TEST 2

TEST 3

etc.





Any thoughts on how I can get it to order the data in numeric order?

You can try this with a dummy field first if you want.



You can take the "TEST" out of the field and just the numeric value, then 

you can change the datatype to Long Integer and

set the format to ""TEST"00000"



good luck,





>>> "B Coluccio" <bcoluccio@m...> 08/15 7:33 PM >>>

In an Access 2000 form, I have an ID_No text field containing data that 

is

currently sorted in the following manner:



TEST 1

TEST 10

TEST 2

TEST 21

TEST 22

etc.



I already have the following as an Event Procedure:

Private Sub Form_Load()

Me.RecordSource =3D "select * from [tblName] where [ID_No] ALike 'TEST%'=20



ORDER BY [ID_No] ASC"





I'd like to order the field to display



TEST 1

TEST 2

TEST 3

etc.





Any thoughts on how I can get it to order the data in numeric order?

Thank you, Roy, for your thoughts. I'm going to look into the Mid$() 

function, and VB code.



Derrick, what I didn't mention before was that I'm looking at a db with 

approximately 4000 records, separated into different cases via the TEST 

<number> differentiation. I have a form that uses some VB and some SQL to 

allow the users to click on an option button in the header to bring up 

which case they want to view. TEST 101, for example. So I don't think your 

suggestion would allow the ability to enter data in multiple cases where 

my example of TEST would change? I'd have them use a TEST 0001 type format 

for data entry, but hard copy documents have been numbered in the TEST 1 

fashion.



> You can try this with a dummy field first if you want.

> 

> You can take the "TEST" out of the field and just the numeric value, 

then 

> you can change the datatype to Long Integer and

> set the format to ""TEST"00000"

> 

> good luck,

> 

> 

> >>> "B Coluccio" <bcoluccio@m...> 08/15 7:33 PM >>>

> In an Access 2000 form, I have an ID_No text field containing data that 

> is

> currently sorted in the following manner:

> 

> TEST 1

> TEST 10

> TEST 2

> TEST 21

> TEST 22

> etc.

> 

> I already have the following as an Event Procedure:

> Private Sub Form_Load()

> Me.RecordSource =3D "select * from [tblName] where [ID_No] 

ALike 'TEST%'=20

> 

> ORDER BY [ID_No] ASC"

> 

> 

> I'd like to order the field to display

> 

> TEST 1

> TEST 2

> TEST 3

> etc.

> 

> 

> Any thoughts on how I can get it to order the data in numeric order?

> 

> 

Try:

dim intNumberid as integer

Dim strFullId as string



strFullid =3D [ID_No]

intNumberid =3D Replace(strFullID,"TEST","")







-----Original Message-----

From: B Coluccio [mailto:bcoluccio@m...]

Sent: Thursday, August 16, 2001 1:57 PM

To: Access

Subject: [access] Re: Ordering in 2000





Thank you, Roy, for your thoughts. I'm going to look into the Mid$()

function, and VB code.



Derrick, what I didn't mention before was that I'm looking at a db with

approximately 4000 records, separated into different cases via the TEST

<number> differentiation. I have a form that uses some VB and some SQL

to

allow the users to click on an option button in the header to bring up

which case they want to view. TEST 101, for example. So I don't think

your

suggestion would allow the ability to enter data in multiple cases where



my example of TEST would change? I'd have them use a TEST 0001 type

format

for data entry, but hard copy documents have been numbered in the TEST 1



fashion.



> You can try this with a dummy field first if you want.

>

> You can take the "TEST" out of the field and just the numeric value,

then =3D

> you can change the datatype to Long Integer and=3D20

> set the format to ""TEST"00000"

>

> good luck,

>

>

> >>> "B Coluccio" <bcoluccio@m...> 08/15 7:33 PM >>>

> In an Access 2000 form, I have an ID_No text field containing data

> that =3D is=3D20 currently sorted in the following manner:

>

> TEST 1

> TEST 10

> TEST 2

> TEST 21

> TEST 22

> etc.

>

> I already have the following as an Event Procedure:=3D20 Private Sub

> Form_Load() Me.RecordSource =3D3D "select * from [tblName] where 

[ID_No]

ALike 'TEST%'=3D20=3D

>

> ORDER BY [ID_No] ASC"

>

>

> I'd like to order the field to display=3D20

>

> TEST 1

> TEST 2

> TEST 3

> etc.

>

>

> Any thoughts on how I can get it to order the data in numeric order?

>

>=20