Time rounding?

[DBFIT]

Hi All

I'm using a simple SELECT query but I want to return/format the time stamp either to the nearest half hour or at least to just ten minute intervals.

EG 19:24 become either 19:30 or 19:20.

Code currently is

"SELECT tblPublications.strPublicationName, tblSubscriptions.intPublicationId, Count(tblPublications.intPublicationId) AS CountOfintPublicationId, tblClients.strClientName, Format([dtTimeStamp],'dd/mm/yyyy hh:m') AS TAD
FROM tblClients INNER JOIN (tblPublications INNER JOIN tblSubscriptions ON tblPublications.intPublicationId = tblSubscriptions.intPublicationId) ON tblClients.intClientId = tblPublications.intClientId
GROUP BY tblPublications.strPublicationName, tblSubscriptions.intPublicationId, tblClients.strClientName, Format([dtTimeStamp],'dd/mm/yyyy hh:m')
HAVING (((tblPublications.strPublicationName)='$publication'))
ORDER BY Format([dtTimeStamp],'dd/mm/yyyy hh:m');"

This code is used for displaying results on a web page.

I've had a suggewstion from another forum about DateDiff but wanted to see if anything else exists for Access.

Thanks

Kev

 

[SkipVought]

One way...

    Dim t As Date, m
    t = #7:24:00 PM#
    m = Format(t, "nn")
    t = TimeValue(Format(t, "hh") & ":" & Int(m / 10) * 10)
    MsgBox t

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[PHV]

to the nearest half hour
You may try this:
Format(CLng(dtTimeStamp*48)/48, 'dd/mm/yyyy hh:nn')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DBFIT]

Cheers chaps - I'll let you know how I go.

 

[DBFIT]

Just tried your suggestion PHV and it worked a treat.

Thanks both