ASKER
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]"
ASKER
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
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
ASKER
ASKER
ASKER
ASKER
So how can I add seconds but not simply add two dates together and halve them
ASKER
ASKER
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.
ASKER
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
Note that the displayed time value always is rounded to the second using normal 4/5 rounding.NewDateCreated = CDate(ItemDateCreated1 + (CDec(ItemDateCreated2) - CDec(ItemDateCreated1)) / 2)
ASKER
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
TRUSTED BY
You might also add an autonumber field to the table, which will give you an order-of-creation sort value. :)