Record re-order by changing date/time created

So far as I know, Access doesn't support any record dragging-and-dropping.  You might be well-served by adding a button to the subform's detail area (so there's a button for each record) that will a) add (or subtract) some amount of time to the CreateDate value and then re-sort the records.  That would permit your users to 'move' records around in the recordset.

You might also add an autonumber field to the table, which will give you an order-of-creation sort value.  :)

OK that sounds good. So what I would need to do is select any record in a list or combo-box and deduct half of the difference between the record selected and the next one down. This is because someone may need to add a number of records between existing numbers. The fastest a record can be added is by duplicating the existing record, minimum likely is 10 seconds. So the closest that any 2 records can be together is 10 seconds and more likely 2 minutes

So My question is really how do I get the date created value from the next record on the list after the one selected?

You could put code like this behind the button:

dim dtActiveCreateDt as date, dtNextCreateDt as date, intSecondsDifference as integer, intSecondsToAdd as integer
'get create date from current record:
     dtActiveCreateDt = me.CreateDt
'get the lowest create date that is after the current create date 
    dtNextCreateDt = DMin("CreateDt", "YourTable", "CreateDt > #" & dtActiveCreateDt & "#")
'get the number of seconds difference between the two dates
    intSecondsDifference = DateDiff("s", dtActiveCreateDt, dtNextCreateDt)
'split the difference
    intSecondsToAdd = intSecondsDifference/2
'change the create date
'note that when passing sql text to DoCmd.RunSQL, quotation marks must be replaced with apostrophes:
    DoCmd.RunSQL "Update YourTable set CreateDt = DateAdd('s', intSecondsToAdd, CreateDt) where [i]criteria[/i]"

Select allOpen in new window

Can you give some more info on the Drag Drop...it can be done but it requires either ActiveX controls (Treeview,Listview i know they support it i have done an application some ages ago) or API calls...but maybe all these might be unneccesarry based on your needs...

This can be achieved by applying priority numbers to the records.
A full solution with a demo is described in detail in my article:

Sequential Rows in Microsoft Access

Browse for the paragraph: 3. Priority Numbers

Thanks John. I was under the impression that Activex controls are frowned upon. I don't know where I got that idea from but is it correct? If you look at Gustaves tables in his article or any table it would just be a case of grabbing one of the rows and dropping it between two existing rows. I guess it would have to be done in a form view.

Gustav that is comprehensive article. I am studying that now

The problem with ActiveX controls is twofold
1. You have to register them on each user machine which becomes a PITA if you have more than a couple of users.
2. They are version dependent.  Many of the 32-bit ActiveX controls were never converted to 64-bit and so you won't be able to use them if your user needs to install the 64-bit version of Office.

All-in-all, unless you can't live without one of these contros, best to just avoid them.

I would stick with the date/time.  You could have the user pick the record they want the selected one to Precede.  Then you can write code to generate a date/time that fits the criteria.

If you go to a sequence number, it must be separate from the autonumber.  I have a couple of apps where I do this.  I generate the sequence number when the user inserts a row and depending on how likely inserts are, I use either 10 or 100 as the increment.  The gap needs to leave room for multiple records to be moved if necessary.  I also give the user a renumber option should a gap become too small.

Here is code from one of my apps that uses a sequence number.  You can modify it to use the date/time.

Private Sub Form_BeforeInsert(Cancel As Integer)
    If Forms!frmLogin!txtAddSecurityLevel >= Forms!frmSwitchboard!sfrmSwitchboard!AddSecurityLevel Then
    Else
        MsgBox "You are not authorized to add data on this form.", vbOKOnly
        Cancel = True
        Me.Undo
        Exit Sub
    End If
    
    Me.SwitchboardID = Forms!frmUpdateMenu!sfrmMenuItems.Form!SwitchboardID
    Me.ItemNumber = DMax("ItemNumber", "[tblSwitchboardItems]", "SwitchboardID = " & Me.SwitchboardID) + 10
End Sub

Select allOpen in new window

this is the code to renumber the existing rows.

Private Sub cmdRenumber_Click()
    Dim ItemCount As Integer
    Dim SeqNum As Integer
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim qd As DAO.QueryDef
    
   On Error GoTo cmdRenumber_Click_Error

    If Me.Dirty = True Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    
    Set db = CurrentDb()
    If Me.txtSwitchboardID & "" = "" Then
        ItemCount = DCount("*", "tblSwitchboardItems", "SwitchboardID = " & Me.Parent!sfrmMenuItems.Form!SwitchboardID)
    Else
        ItemCount = DCount("*", "tblSwitchboardItems", "SwitchboardID = " & Me.txtSwitchboardID)
    End If
    
    Set qd = db.QueryDefs!qAdd1000
        qd.Parameters![EnterSwitchboardID] = Me.SwitchboardID
    qd.Execute      'add 1000 to each item so items can be renumbered without error
    
    Set qd = db.QueryDefs!qRenumberItems
        qd.Parameters![EnterSwitchboardID] = Me.SwitchboardID
    Set rs = qd.OpenRecordset
    SeqNum = 10
    Do Until rs.EOF
        rs.Edit
        rs!ItemNumber = SeqNum
        rs.Update
        SeqNum = SeqNum + 10
        rs.MoveNext
    Loop
    
    Me.Requery
    
   On Error GoTo 0
   Exit Sub

cmdRenumber_Click_Error:

    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdRenumber_Click of VBA Document Form_sfrmDetailItems"
    End Select
    Resume Next
    Resume
End Sub

Select allOpen in new window

Thanks all! Comprehensive as usual.

Some solutions above require additional table fields. As I have a number of existing users it would be good to get a solution without modifying existing data tables.

I have worked a solution but getting weird effects. I created this form. When you select the record that you want to move the current record to, the one above the selection, I should get a new DateCreated value in the form. As you can see I get something else. Selecting Type B in the combo should give me a date between Type b and Type A. The DateCreated should come out at 1:35  
The code is wrong somehow? The query that gets the date above the selection, "Type A",  is a group on max record less than the selected DateCreated. Here is the code:

Option Compare Database

Private Sub Combo8_AfterUpdate()
Dim Secs As Single
Dim NewDateCreated As Single
ItemNumber = Combo8.Column(2)
ItemDateCreated2 = Combo8.Column(1)
ItemDateCreated1 = DLookup("MaxOfItemDateCreated", "RecordOrderQuery")
Secs = (DateDiff("s", ItemDateCreated2, ItemDateCreated1) * 0.5)
NewDateCreated = DateAdd("s", Secs, ItemDateCreated1)
Forms![ProjectForm]![Details].Form![ItemDateCreated] = NewDateCreated
Me.Requery
NewDate = NewDateCreated
End Sub

It must be something wrong with date format, maybe??

Your dates already contain a time element so your code is finding the middle between the two times in seconds, you are then adding it to the existing seconds of the earlier time.  Look at the actual data values in your calculations to see what you are actually using as arguments.

When you are working with a date field, you are working with a double precision number.  Formatting has nothing to do with anything.   Formatting is how we display a date/time for humans because humans don't process 23345.455556666 as a date, but it is :)

If you Format() a date/time field BEFORE using it in a calculation, you have actually converted it to a string.  Access is pretty smart so it may still recognize the string as a data but don't count on it.

Thanks Pat. The error was simply that the line:

Secs = (DateDiff("s", ItemDateCreated1, ItemDateCreated2) * 0.5)

I found it checking the formats as you suggested. I had the first and second dates around the wrong way so the result was -300 seconds instead of +300seconds. I should have figured that out.

Here is the query that supplies the previous record's DateCreated from the one selected in the combobox:

SELECT ItemQuery.ItemNumber, First(ItemQuery.ItemDateCreated) AS FirstOfItemDateCreated, ItemQuery.DoorNumbers
FROM ItemQuery
GROUP BY ItemQuery.ItemNumber, ItemQuery.DoorNumbers
HAVING (((First(ItemQuery.ItemDateCreated))<[Forms]![RecordOrderForm]![ItemDateCreated2]))
ORDER BY First(ItemQuery.ItemDateCreated) DESC;

Surprisingly (to me) this does not return only the first record in the set, It supplies all of them. Changing First to Max also gives me all of the records that are less than the combobox selection. So I am not sure what First or Max means in this example. Fortunately in each case the DoLookup chooses the DateCreated field from the first record in the query. It all now works, unless:

So if you or anyone can help me, I just need a code idea for when the query runs. It will only return a value as long as there is a record above the one selected. So if the user want to move the current record above the top of the combobox selection then I will get an error.  How do I fix this?

First and Last are pretty useless as functions since they simply return the value in the first or last row of your dataset.  What record is first or last is somewhat dependent on how the recordset is sorted but if what you really want is the "earliest" or "lowest" value, you should use Min() and if you want the "latest" or "highest" value, you should use Max()

Gus' code should solve your problem.

Thanks Pat

Something I don't understand is although the DateCreated values are taken from comboboxes I'm allowed to add seconds to either but a more logical way to get a value between two values is to add the values together and divide by 2, or, as I prefer, multiply by 0.5 to eliminate any chance of divide by Zero error. So how can I add seconds but not simply add two dates together and halve them

So how can I add seconds but not simply add two dates together and halve them

You could get away with it now, but it would fail at about year 5950(!).
The method I showed - using the difference - will be safe for any dates.

Thanks Gustav.  I'm using your code. I just do not understand how I can add second to a date but not simply add 2 dates together?

Not sure what you mean. Normally, it doesn't make much sense to add two dates together, except if the two values represent two timespans.

As used in this context I'm using the DateCreated field to order records in the order in which they were created. But occasionally something gets missed and a record that should be in a particular place is created at a different Date/time. So to move it I want to simply change the datecreated field to a date in between the two dates selected. Currently we use DateDiff to get the difference between dates in Seconds Divide that number by 2 and add it to the earlier date to create a new DateCreated that is half way between the two. A simpler way would be to add the two different dates together and divide by 2 this will always put the new date equally between them

Currently we use DateDiff to get the difference between dates in Seconds Divide that number by 2 and add it to the earlier date to create a new DateCreated that is half way between the two.

Both dates have a time element so you can't add the seconds to the first date UNLESS you strip off the time first.

Converting the two dates to double, adding them together, dividing by 2, and converting the result back to a date data type should work.  But this isn't date arithmetic so you would not use date functions such as DateAdd and DateDiff to do the calculation.

So:

NewDate = cdate(cdbl(date1) + cdbl(date2) / 2)

Might work for you.

Earlier I reminded you that dates are internally stored as double precision numbers so you'd think you could just do this with dates directly and you might be able to.  
NewDate = (date1 + Date2) /2
Try it.  I'm not at my computer so I can't try it for you.

Even though the first expression is more complex, your intention would be more obvious.

Thanks Pat. I did try the latter and I got error. So I will try your better way suggestion

As your date value have both a date part and a time part, and if the time part holds a precision to the second, you should stick with DateDiff and DateAdd, as performing raw math on the date values may introduce a millisecond part (which DateTime is capable of).
Apart from the special cases where you wish to store a millisecond part, you should always take care, that stored date values are rounded to the second.
DateAdd will do this, even if you pass a decimal count of seconds, as the count will be rounded to the nearest even integer using Banker's Rounding.

Here are two examples displaying the differences:

ItemDateCreated1 = #2020-10-02 08:00:01#
ItemDateCreated2 = #2020-10-10 13:00:02#

Seconds = DateDiff("s", ItemDateCreated1, ItemDateCreated2)
NewDateCreated = DateAdd("s", Seconds / 2, ItemDateCreated1)
? NewDateCreated, Seconds / 2, DateDiff("s", ItemDateCreated1, NewDateCreated)
2020-10-06 10:30:01          354600.5      354600 

NewDateCreated = CDate(ItemDateCreated1 + (ItemDateCreated2 - ItemDateCreated1) / 2)
? NewDateCreated, Seconds / 2, DateDiff("s", ItemDateCreated1, NewDateCreated)
2020-10-06 10:30:01          354600.5      354601 

NewDateCreated = CDate((ItemDateCreated1 + ItemDateCreated2) / 2)
? NewDateCreated, Seconds / 2, DateDiff("s", ItemDateCreated1, NewDateCreated)
2020-10-06 10:30:01          354600.5      354601 

ItemDateCreated1 = #6020-10-02 08:00:01#
ItemDateCreated2 = #6020-10-10 13:00:02#

Seconds = DateDiff("s", ItemDateCreated1, ItemDateCreated2)
NewDateCreated = DateAdd("s", Seconds / 2, ItemDateCreated1)
? NewDateCreated, Seconds / 2, DateDiff("s", ItemDateCreated1, NewDateCreated)
6020-10-06 10:30:01          354600.5      354600 

NewDateCreated = CDate(ItemDateCreated1 + (ItemDateCreated2 - ItemDateCreated1) / 2)
? NewDateCreated, Seconds / 2, DateDiff("s", ItemDateCreated1, NewDateCreated)
6020-10-06 10:30:02          354600.5      354601 

NewDateCreated = CDate((ItemDateCreated1 + ItemDateCreated2) / 2)
? NewDateCreated, Seconds / 2, DateDiff("s", ItemDateCreated1, NewDateCreated)
6020-10-06 10:30:02          354600.5      354601 

Select allOpen in new window

Note that the displayed time value always is rounded to the second using normal 4/5 rounding.

I can add, that should one need to maintain a precision to the millisecond, the only method would be the last, and converting to Decimal would be needed:

NewDateCreated = CDate(ItemDateCreated1 + (CDec(ItemDateCreated2) - CDec(ItemDateCreated1)) / 2)

Select allOpen in new window

This is very strange

When I use NewDate = CDate(CDbl(ItemDateCreated1) + CDbl(ItemDateCreated2) / 2)

Where both dates are 2019 I get 2079

If I use NewDate = CDate((ItemDateCreated1+ ItemDateCreated2) * 0.5)  I get correct result. Both dates are formatted correctly

I want to use double because it allows me to split seconds

The first expression does not have the parentheses in the right place.  You need to add the two numbers BEFORE dividing by 2.  You are adding the first number to the second number divided by 2.


NewDate = CDate((CDbl(ItemDateCreated1) + CDbl(ItemDateCreated2)) / 2)