Annie,
What you say suggests that maybe it would help to add a field that
contains the date & time that a record was entered into the system,
instead of a sequence number. Then you could sort on that basis. (You
wouldn't have to display it, it would just keep the records in order.)
If it's a Report that is doing the ordering, that is likely to
overrule any "sort by" property defined in your Query.
An "ORDER BY" in SQL is the same as the "Sort:" property in Query
Design View. If someone posts some SQL here, and your database contains
the Tables to which it refers, you can use it by doing "Create query in
Design view", close the "Show Table" dialogue box, switch to SQL View,
then copy the SQL from the message and paste it into SQL View. If the
Tables are all there, you can then switch back to Query Design View to
edit the Query. If the Tables are NOT all there, you'll get messages
saying so, and this method won't work.
Incidentally, the code listed in the messages that you cited was
mostly VBA, contained in a Module. Only a small part (the parts
beginning with "select") was SQL.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
Hi there,
I would like to do exactly what you mention there, i.e. to keep my query
records in the same order - the problem being that they are order entry &
critical in that order, but once I open a report which has a 'group by' then
I lose my order entry order.
However, I have no idea how to use SQL - I only do queries the 'box' way
Please can anyone help me?
:
One small addition would be to add an ORDER BY clause to the SQL statement
if the order of the records is important.
Set MyRec = MyDb.OpenRecordset("select [MyNumber] From tableName ORDER BY
FieldA, FieldB, FieldC")
I mentioned this because the OP said "...and doing all the proper
sorting...". That leads me to believe that the order of the records is
important in assigning the numbers.
You can use this function to update the field
Function FunctionName() As Integer
Dim MyDb As DAO.Database, MyRec As DAO.Recordset, I As Double
I = 0
Set MyDb = CurrentDb
' Open the table
Set MyRec = MyDb.OpenRecordset("select [MyNumber] From tableName")
While Not MyRec.EOF
MyRec.Edit
I = I + 1
' Add a number
MyRec!MyNumber = I
MyRec.Update
MyRec.MoveNext
Wend
End Function
before running the function please back up your data
--
If I answered your question, please mark it as an answer. That way, it
will
stay saved for a longer time, so other can benifit from it.
Good luck
:
Hi,
I am puzzled and can not figure out how to generate an incrementing
number
and insert it into a table. I have a static table, which I create from
importing text fields and doing all the proper sorting. Once I am
finished,
I would like to create a number that will be incremented by one and
insert it
into the table starting with record number 1 all the way to the last
record.
I don't want to use Autonumber, because once done it will not allow any
edits
to that column. I need to create this table and then later add records
that
will have inconsistent numbering process. Basically it's a priority table
that allows for special codes, all numbers.
Tahnks a million,
Eli
