Avatar of Michael Larimore
Michael LarimoreFlag for United States of America

asked on 

Extract exact time and categorize

I have a field that is formatted like "9/9/2016 8:00AM".  I would like to use this field to categorize whether an entry is "On Time" or "Late".  So far I am able to say that any thing after the hour of 5AM is late, however this categorizes the the 5:00AM time as "Late".  We also have two shifts/day so my code contains a nested IF to look at both 5am and 2pm.  

Ideally it would count if anything between 5:01AM and 12:00PM and between 2:01PM and 11:00PM as "Late" in the "Status" column.  Here is my code so far:

SELECT EmpNumber, FirstName, LastName, ClockIn, ClockOut, IIf((DatePart(hour,ClockIn) Between 5 And 12) Or (DatePart(hour,clockin) Between 14 And 21),'Late','On Time') AS Status

FROM v_MES_TcActivities

GROUP BY EmpNumber, FirstName, LastName, ClockIn, ClockOut, IIf((DatePart(hour,clockin) Between 5 And 12) Or (DatePart(hour,clockin) Between 14 And 21),'Late','On Time'), CostCtr

HAVING (((ClockIn) Between Getdate()-30 And Getdate()) AND ((IIf((DatePart(hour,clockin) Between 5 And 12) Or (DatePart(hour,clockin) Between 14 And 21),'Late','On Time')) Like 'Late') AND ((CostCtr)='106330'))

ORDER BY LastName, ClockIn DESC

Open in new window

Microsoft AccessMicrosoft SQL Server

Avatar of undefined
Last Comment
Michael Larimore
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Michael Larimore

ASKER

Thank you that is perfect!!  I built the code using access query builder which usually adds in the default "group by" function for some reason.
Microsoft Access
Microsoft Access

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.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo