Move current record to end of table based on text value in field

[andrewfly]

Simple problem, I think...just never had to do anything like this...

Essentially, if the text in the field [HSTemp] starts with CT, I would like to move this record to the end of recordset (making it the last record).

Once I find the CT flag, I can't seem to get the record to move to the end of the recordset...any suggestions?

Thanks for the help!

 

[Newman]

Add a numeric field to your recordset, then...

if left([HSTemp];2) = "CT" then
FlagField = 1
else Flagfield = 0
endif

Then sort your recordset by Flagfield.

 

[andrewfly]

Interesting solution...never thought of that...I'll give that a go, but, is there any way you know of to actually move the record to the end...essentially, lets say...

Im sifting through records A-Z, I come to record D, but it is dependent upon a calculation that is performed in record R. Therefore, I would like to throw record D at the end of the table so it can then use the results of record R.

My application performs thousands of calculations on random parts list that are in no specific order. Recently I have been asked to add a functionality that will allow the user to say, 'hey, instead of using the regular boundary conditions, why not say the boundary conditions for this one part are the results of another part'.

I know I can solve it using arrays and all, but the CT identifier tells me that the part will need the results from another part that may have, or may have not been calculated. Seemed like just finding the CT parts and throwing them at the end would be an extremely simple solution...I guess what you suggessted would let me put those at the end with the addition of the new column. Any other ideas?

 

[boblarson]

One thing you should know - Access really doesn't store records in any particular order. It can display them in an order you define.

Check out this post by The_Doc_Man for some info on that:
http://www.access-programmers.co.uk/forums/showpost.php?p=601014&postcount=3

 

[andrewfly]

So i guess what this says is that I can, as Newman suggested, add some numeric value into this new field, and then have an appropriate orderby criteria on the form to force these records to the end.

Thanks boblarson

 

[The_Doc_Man]

Thanks for using another post of mine, Bob.

This is a case where the "Old Programmer's Rules" come into play. "Access can't tell you anything you didn't tell it first."

If you want to see something in a particular order, you have to tell Access something that exhibits that particular order. So... yes - add a numeric field to become your presentation order - particularly if your codes like "CT" are not to be sorted alphabetically.

Remember this fact: Tables have only one order naturally. That order is the order in which the records are added UNLESS there is a prime key on the table and you have compacted the database very recently. All other orders are simple artifacts of the "ORDER BY" clause used in the query that displays the table contents. They are as ephemeral as the open query. That is, the ordering vanishes the moment you close the query.

 

[andrewfly]

Doc,

One quick question for you. I have added the sort field, and all works well. However, out of curiosity, I was wonderinf if you had any suggestions to remove a step from my code.

I've learned that you can not execute an SQL statement on a table unless it somehow physically changes the table...

So, I can not write SQL just to modify order of the records, and execute this via VBA. However, I can create a maketable query, and generate a new table with the necesarry sort order. Is there an easy way to simply re-sort the table based on the CT column, and not have to create this new table?

Thanks for the help, much appreciated.

 

[modest]

You can just use an UPDATE Query. What exactly are you trying to do?


If you actually wanted to put an order on the CT record, use the ORDER BY in your SQL and use this:

IIF(
  [Field]="CT",
  IIF(DMax("[Number Field]","Table") & ""<>"",
      DMax("[Number Field]","Table") + 1,
      1
  ),
  0
)

 

[andrewfly]

Just change the order of records in a table based on the data in the CTSort column...a simple sort via vba (or sql).

 

[modest]

Just change the order of records in a table based on the data in the CTSort column...a simple sort via vba (or sql).

You cannot change the order of records in a table. When records are pulled from memory, they are loaded in order of most quickest retrieved; they are not retrieved on a particular order. It just appears that they are normally in order based on how they are loaded into memory and their memory location.

If you go into the table's design window, from the File-Menu toolbar goto View >> Properties and you'll see an order-by field in the property box. This is the initial one Access uses when printing to screen.

If you want to order records in a table, you will need to use "SELECT * FROM [Table Name] ORDER BY [some field]". If you want to work with an ordered table, you will need to work with a query that is based on the table, or load the table into a temporary recordset in VBA (i.e. DAO.Recordset or ADO.Recordset)

-----------------------------------

When I asked what you were trying to do, I was trying to see if this data was going to a report, or if you were binding a form on the table, or trying to do something else like run calculations.

 

[andrewfly]

The table is generated dynamically each time the database is run.

This application should be a standalone visual basic application, however it was handed to me as is...it is more of an application than a database.

I've done what modest suggested, and load the table into a temporary recordset in VBA...suited my needs better (wrote a query to generate a new table with the proper sort order). Thanks for the help all, it is much appreciated!