calculating time values in query

i have attached sample db.
open database- the query [qryRoundTo8]-is a create table query- it creates [tblMain]-
it queries tbl1M- and if the ClockIn field is 8:05 or less- it changes it to 8:00

I want to change it so - if it is 7:45 am to 7:59 am- it also changes to 8:00 ( both conditions cause it to change to 8:00 am
I also want to change ClockOut Field to go back to 4:30 pm - if it is between 4:31 pm to 4:45 pm.

thank you
TestA.accdb
davetoughAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GRayLCommented:
Are you tring to round the times to the nearest quarter hour?  If so, what are the rules for the other three?  In fact after reading your question again, I really have no idea what you are asking.  Please try again.
0
davetoughAuthor Commented:
between 7:45 am and 8:05 am - I want time display 8:00 am
for ClockIN

between 4:30 pm and 4:45 pm- i want time to display 4:30 pm for TimeOut

thank you
0
GRayLCommented:
And what about the 8:07 arrival and the 4:47 departure?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

almanderCommented:
This formula should work, just replace with the date fields for the other values.

In: DateAdd("n",Round(DateDiff("n",DateValue([MaxOfIn]),[MaxOfIn])/60,0.5)*60,DateValue([MaxOfIn]))
0
davetoughAuthor Commented:
8:07 arrival should be 8:07
and 4:47 should be 4:47
only thing changes when it is between 7:45 and 8:05   / and 4:30 and 4:45

almander:
I plugged in your code for clock in- thought it was working - but when I used 7:44 it still displayed 8:00 and when I used 8:06 - it still went back to 8 -
maybe i am not doing correctly
0
Michael VasilevskySolutions ArchitectCommented:
You could use multiple queries or one custom function like:

Function UpdateTime(dTime As Date) As Date

        If TimeValue(dTime) > #7:45:00 AM# And TimeValue(dTime) <= #8:05:00 AM# Then
            UpdateTime = #8:00:00 AM#
        Else
       
            If TimeValue(dTime) > #4:31:00 PM# And TimeValue(dTime) <= #4:45:00 PM# Then
                UpdateTime = #4:30:00 PM#
               
            Else
                UpdateTime = TimeValue(dTime)
            End If
        End If

End Function

See copy of query in the attached.
TestA.accdb
0
Patrick MatthewsCommented:
This seems to be working:


SELECT fdate, 
    fname, 
    fcode, 
    IIf(TimeValue([MaxOfIn]) >= #7:45:00# And TimeValue([MaxOfIn]) <= #8:05:00#, DateValue([MaxOfIn]) + #8:00:00#, [MaxOfIn]) AS ClockIn, 
    MaxOfLunch AS Lunchtime, 
    MaxOfRLunch AS Returned, 
    IIf(TimeValue([MaxOfOut]) > #16:30:00# And TimeValue([MaxOfOut]) <= #16:45:00#, DateValue([MaxOfOut]) + #16:30:00#, [MaxOfOut]) AS ClockOut, 
    Below 
INTO tblMain
FROM tbl1M;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.