Including a time measument in query

meggiej

New member
Local time
Today, 09:19
Joined
Sep 6, 2016
Messages
2
Hi

Sorry not sure on the title

I am new. The last time I used access was when I was in secondary school 9 years ago. Please would you be able to help me with a query

I have made a table for when my staff are late, it includes:

Employee Name
Date
Start time
Time of text
Arrival time
Notes
Lateness - this is a calculated field, arrival time-start time

I do want to make a query of showing how many times particular employees are more then 5 minutes late. I have tried ">#00:05:00#". I have no clue what to put
 

Attachments

  • carer access.PNG
    carer access.PNG
    5.4 KB · Views: 130
  • carer access2.PNG
    carer access2.PNG
    9.1 KB · Views: 144
Use the DateDiff function . DateDiff('n',FirstDateTime, SecondDatetime) will return the difference in minutes between two DateTime fields.
https://support.office.com/en-gb/ar...e24fc238f85f?ui=en-US&rs=en-GB&ad=GB&fromAR=1

Before you do that though, change your Date field name as Date is a reserved word and will cause you lots of problems later on. In fact get used to not using spaces and other characters, it makes any coding much more troublesome. Have a look on here for naming conventions, and reserved words.
 
That is not what I mean. Perhaps I did not explain it right

I do not want to know the difference between two dates/times i want the query to pick out all of the times that the employees have been eg.more then 5 minutes late
 
Yes - to do that create a query that will list the time in minutes they are late, then simply put > 5 in the criteria. The difference created by DateDiff is simply a number, no need to mess about with formatting or your calculated field.

How is your calculated field generated?
 
Since, you have calculated the difference in minutes you can use the expression diff>#00:05:00# to filter the records.

Or use the expression Int(diff*1440)>5 to filter the record.
 
I would not strore calculated fields in your table. Do what Minty said and calculate in the query
 

Users who are viewing this thread

Back
Top Bottom