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"")

 

[welshgasman]

In the other thread it says this but this doesn’t always rounddown
TimeSerial(Hour([mytime]),(Minute([mytime])\30)*30,0)

my created date time has a date and time in there so my aim was to take the time from
That and put into a category by rounding down to nearest half hour

i thought it would be a lot simpler to do like in excel using the Floor Function

any help with my example would really be appreciated

thank you

You need to supply examples?, as it appeared to work for me.?

 

[welshgasman]

I generated some data.
The query was

SELECT Transactions.ID, DateAdd("n",[ID],[TransactionDate]) AS Expr1, TimeSerial(Hour(DateAdd("n",[ID],[TransactionDate])),(Minute(DateAdd("n",[ID],[TransactionDate]))\30)*30,0) AS Expr2
FROM Transactions;

The results (well only enough to show it works)

 

[mahmed1]

I generated some data.
The query was

SELECT Transactions.ID, DateAdd("n",[ID],[TransactionDate]) AS Expr1, TimeSerial(Hour(DateAdd("n",[ID],[TransactionDate])),(Minute(DateAdd("n",[ID],[TransactionDate]))\30)*30,0) AS Expr2
FROM Transactions;

The results (well only enough to show it works)
View attachment 17633
View attachment 17634

Here is my code trying to implement your code

CreatedDateTime is stored like this
22/06/2020 15:45:23 Etc

Format(TimeSerial(Hour([CreatedDateTime]), Minute([CreatedDateTime])\30)*30,0),””hh:nn””)

Is this correct?

 

[welshgasman]

Here is my code trying to implement your code

CreatedDateTime is stored like this
22/06/2020 15:45:23 Etc

Format(TimeSerial(Hour([CreatedDateTime]), Minute([CreatedDateTime])\30)*30,0),””hh:nn””)

Is this correct?

Well have you tried it? as when I used the Format as well yesterday, that was the format that was displayed?

 

[welshgasman]

Be aware, if you use Format() you will turn the time into a string.? I'd probably leave it as a time value and format when needed.?
Up to you.

 

[mahmed1]

The main thing i wernt sure about was weather i needed to strip off the date element by using INT and also whether i needed to use rounddown outside the formula u provided

 

[welshgasman]

The main thing i wernt sure about was weather i needed to strip off the date element by using INT and also whether i needed to use rounddown outside the formula u provided

You are already stripping off the date element as you are only using the hour minute and seconds?
The formula linked to provides the rounddown, so I am at a loss as to what is the issue?

 

[mahmed1]

Sorry my bad - the syntax i put was wrong with the double quotesNot applied properly

i double double quoted it

thank you and ill give that a go

 

[mahmed1]

Im getting this error message

Syntax error in update statement (I update the table once i import it in) in VBA

DoCmd.RunSQL "UPDATE tbl_Email_Rolling " & _
"SET ModifiedDate = INT([ModifiedDateTime]), " & _
"ModifiedPeriod = Format(TimeSerial(Hour([ModifiedDateTime]), Minute([ModifiedDateTime])\30)*30,0),""hh:nn"")"

 

[welshgasman]

Put the sql into a string and debug.print that string. That will show you what is wrong.

From here it looks like should should be concatenating your rounded tme with the text.