Access Round down not working 100%

[mahmed1]

Hi All,

What im trying to do is round down to the nearest half half hour

so if createddatetime is say 03/07/2020 00:05:27 then the period should fall in 00:00:00
if its 00:31:00 then it should fall in 00:30:00
if its 01:01:00 then 01:00:00, if 01:59:57 then 01:30

Hope this makes sense as occasionally the formula below is catagorising into 00:05 and 00:25etc

This is my Formula in VBA

CreatedPeriod = FORMAT(CStr(ROUND(([CreatedDateTime] - INT([CreatedDateTime]))*48,0)/48), ""hh:nn"")

 

[mahmed1]

cool will do that - are you saying i need to wrap the formula in cStr()?

 

[welshgasman]

No. To keep things simple I'd assign the format etc to a string and use that in your RunSQL.
Plus DO NOT keep changing my code
The Minute had a ( before it and you removed it? It took me ages to find out what worked no longer worked.

Whenever you are constructing SQL strings like this, debug.print them first until it is correct, then either remove or comment out.

Sub testSQL()
Dim strSQL As String
Dim strTime As String
Dim ModifiedDateTime As Date

ModifiedDateTime = Now()
strTime = Format(TimeSerial(Hour(ModifiedDateTime), (Minute(ModifiedDateTime) \ 30) * 30, 0), "hh:nn")
strSQL = "UPDATE tbl_Email_Rolling "
strSQL = strSQL & "SET ModifiedDate = DATEVALUE([ModifiedDateTime]), "
strSQL = strSQL & "ModifiedPeriod = '" & strTime & "'"

Debug.Print strSQL

DoCmd.RunSQL strSQL
End Sub

 

[mahmed1]

No. To keep things simple I'd assign the format etc to a string and use that in your RunSQL.
Plus DO NOT keep changing my code
The Minute had a ( before it and you removed it? It took me ages to find out what worked no longer worked.

Whenever you are constructing SQL strings like this, debug.print them first until it is correct, then either remove or comment out.

Sub testSQL()
Dim strSQL As String
Dim strTime As String
Dim ModifiedDateTime As Date

ModifiedDateTime = Now()
strTime = Format(TimeSerial(Hour(ModifiedDateTime), (Minute(ModifiedDateTime) \ 30) * 30, 0), "hh:nn")
strSQL = "UPDATE tbl_Email_Rolling "
strSQL = strSQL & "SET ModifiedDate = DATEVALUE([ModifiedDateTime]), "
strSQL = strSQL & "ModifiedPeriod = '" & strTime & "'"

Debug.Print strSQL

DoCmd.RunSQL strSQL
End Sub

Thank You