Access: keep in numeric order 1

[ivs7720]

Hi,

Thank you in advance for reading this.

I have a text file with ascending numeric values. I converted to access. It needs to be on ascending numeric value using the numeric value from the text file. I made the field as primary key but when I wrote a code (module) to start from first record, it does not follow the ascending order at all. But it looks like in ascending order visually. How can I keep the numeric order in access?

 

[Blorf]

Is your primary key a text field?

If so, numbers like 1, 11, 111 will appear before 2, 3, 4.

Is that what's happening?

If not, what is the name of your field?

If you are using something like :

Set X = currentdb.openrecordset("YourTable")

then make sure you are using your index for order, like

X.index = "YourPrimaryFieldName"

Hope this helps,
ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[ivs7720]

Thank you Chaz for your reply.

My primary key is number.

Yes. I used the Set X = currentdb.openrecordset("YourTable")

When I did what you asked me to do, it failed and the message is "Run time 3800. "id" is not an index in this table". Please see part of the code below. Thank you for your help. As you can tell I am just starting to learn VBA.

Dim dbs As Database
Dim rs1 As Recordset
Set dbs = CurrentDb

Set rs1 = dbs.OpenRecordset("test")

rs1.Index = "id" **** it fails here *****
rs1.MoveFirst

....More codes

 

[ivs7720]

Chaz,

I forgot to tell that on table design view, I set the id field's indexed to "Yes no duplicates".

Thank you.

 

[Blorf]

If you open your table in design view, there is a button for index, kinda looks like a lightning bolt.

If you click it, you can see what access has named the index for the table. Just change the .index = "" to what ever the index name is.

Hopefully that will work.

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[ivs7720]

ChaZ,

Thank you so much! It works!

 

[Blorf]

Cool, glad to help.

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert