table problem

[Guest]

I have a table for months. When I enter the months name starting of Jan, Feb,
March, April……., Dec the order of the months change to be random. For
example: April, Aug. Dec. Jan…….
How can I make the data stay as typed?
your help is highly appreciated.
thx.

 

[Guest]

Hi Ghost,

You will need to add some sort of custom sorting column, otherwise you
cannot guarantee that the data will be returned in any particular order - not
even "in the order it was entered".

Add a column called SortOrder and number them in the order you want them in.

Damian.

 

[Guest]

Hi,
Thanks a lot
It works

Hi Ghost,

You will need to add some sort of custom sorting column, otherwise you
cannot guarantee that the data will be returned in any particular order - not
even "in the order it was entered".

Add a column called SortOrder and number them in the order you want them in.

Damian.

 

[Jamie Collins]

You will need to add some sort of custom sorting column, otherwise you
cannot guarantee that the data will be returned in any particular order - not
even "in the order it was entered".

I'm not so sure the order the OP is seeing is random: it could be
alphabetical. I too offer no guarantee but I would wager the following
code *always* spits out the months in alpha order, even though that is
not the order of insertion:

- Show quoted text -

Sub TablePhysicalOrder()
'Kill Environ$("temp") & "\DropMe1.mdb"
'Kill Environ$("temp") & "\DropMe2.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
cat.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe1.mdb"
Set cat = Nothing

Dim con
Set con = CreateObject("ADODB.Connection")
With con
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe1.mdb"
.Open

' INSERT in 'chrono' order
.Execute _
"CREATE TABLE Months (" & _
"short_name CHAR(3) NOT NULL PRIMARY KEY);"
.Execute _
"INSERT INTO Months VALUES ('Jan');"
.Execute _
"INSERT INTO Months VALUES ('Feb');"
.Execute _
"INSERT INTO Months VALUES ('Mar');"
.Execute _
"INSERT INTO Months VALUES ('Apr');"
.Execute _
"INSERT INTO Months VALUES ('May');"
.Execute _
"INSERT INTO Months VALUES ('Jun');"
.Execute _
"INSERT INTO Months VALUES ('Jul');"
.Execute _
"INSERT INTO Months VALUES ('Aug');"
.Execute _
"INSERT INTO Months VALUES ('Sep');"
.Execute _
"INSERT INTO Months VALUES ('Oct');"
.Execute _
"INSERT INTO Months VALUES ('Nov');"
.Execute _
"INSERT INTO Months VALUES ('Dec');"
.Close
End With

Dim jeng
Set jeng = CreateObject("JRO.JetEngine")
jeng.CompactDatabase _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe1.mdb", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe2.mdb"

Dim rs
Set rs = CreateObject("ADODB.Recordset")
rs.Open _
"SELECT short_name FROM Months;", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe2.mdb"

' Compact sorts data into PK order:
MsgBox rs.GetString
End Sub

Jamie.

--