Movefirst statement problem 1

[oro77]

Hello,

I have a problem to use correctly this statement.

I have one table with 2 different fields A and B.
When I open the table, I can see that the table is sorted according A by double clicking on the table under Access.

Then I use the Movefirst statement and display my position by using the msgbox but it does not display the first value of the table. In fact it displays the smallest value of B field. I do not know why but for the program, it is like the table is sorted according B.

Here is the simple code :
If rstTable.RecordCount <> 0 Then
rstTable.MoveFirst
End If
MsgBox rstTable![A]

So I would like to know how to use Movefirst to have the smallest value for the A field.

Thank you very much in advance.

 

[pjm]

Tables in a relational database don't have any guaranteed order.

If you want sorted data you must use a query with an "order by" clause.

e.g.

dim rst as dao.recordset
set rst = currentdb.openrecordset ("select a, b, from t order by a")

HTH
pjm

 

[oro77]

Thank you for your quick reply, I will test that !

 

[oro77]

A bit late but I just found why my table acted odd. Maybe it could help someone who may have the same problem.

When you open the table in the design mode, on every field, the "index" parameter must be set OFF. Then there won't be any sort problem. In fact, sometimes Access set it ON for some reason by default.

(I am not sure of the parameter name in english, my access is set in an other language)

 

[pjm]

You probably have some entries in "autoindex on import/create" in tools/options/tables/queries, so Access will have created indexes when you created columns.

Be warned though - a table in an RDBMS does not guarantee any ordering of rows. The database is free to return rows in any order it wants to. If you want ordered data you must use an "order by" clause in a query. If you disregard this then one day you will be bitten

pjm

 

[oro77]

Ok thank you for the information. I take note but I cannot make this change now. I have so many table to change But if I have a problem with order, I will know where it comes from.