Time Query to handle after 12 am Logouts

[MariaL]

I am creating a report based on a query that subtracts a persons Login Time
from their Log Out Time: Currently the query has this expression
Hours: TimeValue([import]![Logout Time])-TimeValue([Login Time])
This works fine if the person logs in and logs out the same day. But if the
person logs in at 9:31 pm and logs off at 12:43 am (the next day but the same
shift) I am getting a result of 20:48 hours. I need to have a function that
will handle the after midnight logouts so the total work hours won't be
skewed in the report that I create.

Thanks

 

[Jeff Boyce]

Maria

Consider using the DateDiff() function, with the "nn" (minutes) level of
precision. Are your In/Out fields formatted as Date/Time? If so, an actual
Date AND Time are being entered.

Good luck

Jeff Boyce
<Access MVP>

 

[MariaL]

Jeff,
Thanks for your reply but I am still stuck and need help.
When I import the times into the database from a speadsheet I don't have a
date value in the field the time is imported in a military time format.
Example login time = 18:00 logout time = 0:23

I was thinking that creating a function within a module would be the best
way to go. How do you call a function within a query?

I appreciate the help but can you be a little more detailed in your reply.
I need all the help I can get.

Thanks again
Maria

Maria

Consider using the DateDiff() function, with the "nn" (minutes) level of
precision. Are your In/Out fields formatted as Date/Time? If so, an actual
Date AND Time are being entered.

Good luck

Jeff Boyce
<Access MVP>

I am creating a report based on a query that subtracts a persons Login Time
from their Log Out Time: Currently the query has this expression
Hours: TimeValue([import]![Logout Time])-TimeValue([Login Time])
This works fine if the person logs in and logs out the same day. But if
the
person logs in at 9:31 pm and logs off at 12:43 am (the next day but the
same
shift) I am getting a result of 20:48 hours. I need to have a function
that
will handle the after midnight logouts so the total work hours won't be
skewed in the report that I create.

Thanks

 

[Jeff Boyce]

Maria

How Access displays (i.e., formats) a date/time field and how it stores the
underlying value are not (necessarily) the same.

If you are importing values from Excel, what is the data type Access is
using for those values?

How are you importing (Get External Data | Import)? Or are you "linking"?

Are you "parsing" the incoming data via a query or using it as is?

More info, please...

Jeff Boyce
<Access MVP>

Jeff,
Thanks for your reply but I am still stuck and need help.
When I import the times into the database from a speadsheet I don't have a
date value in the field the time is imported in a military time format.
Example login time = 18:00 logout time = 0:23

I was thinking that creating a function within a module would be the best
way to go. How do you call a function within a query?

I appreciate the help but can you be a little more detailed in your reply.
I need all the help I can get.

Thanks again
Maria

Maria

Consider using the DateDiff() function, with the "nn" (minutes) level of
precision. Are your In/Out fields formatted as Date/Time? If so, an
actual
Date AND Time are being entered.

Good luck

Jeff Boyce
<Access MVP>

I am creating a report based on a query that subtracts a persons Login
Time
from their Log Out Time: Currently the query has this expression
Hours: TimeValue([import]![Logout Time])-TimeValue([Login Time])
This works fine if the person logs in and logs out the same day. But
if
the
person logs in at 9:31 pm and logs off at 12:43 am (the next day but
the
same
shift) I am getting a result of 20:48 hours. I need to have a function
that
will handle the after midnight logouts so the total work hours won't be
skewed in the report that I create.

Thanks

 

[MariaL]

Jeff,
Initally the log times are exported from an Avaya phone log into a text
format.
Example:
Agents Name 54744 6:02PM 6:48PM 4/28/2005
A macro imports the text file into an Excel document.
Example:
Agents Name 54744 6:02PM 12:15AM 4/29/2005

But occasionally the agents logs in one day and ends up logging off after 12
am the next day. The logoff needs to be counted as one shift but it ends up
skewing the login hours for the next day:

To answer your question the Excel Spreadsheet is imported directly into a
table using the TransferSpreadsheet action. The format of the information
looks like above.

Thanks,

Maria

How Access displays (i.e., formats) a date/time field and how it stores the
underlying value are not (necessarily) the same.

If you are importing values from Excel, what is the data type Access is
using for those values?

How are you importing (Get External Data | Import)? Or are you "linking"?

Are you "parsing" the incoming data via a query or using it as is?

More info, please...

Jeff Boyce
<Access MVP>

Jeff,
Thanks for your reply but I am still stuck and need help.
When I import the times into the database from a speadsheet I don't have a
date value in the field the time is imported in a military time format.
Example login time = 18:00 logout time = 0:23

I was thinking that creating a function within a module would be the best
way to go. How do you call a function within a query?

I appreciate the help but can you be a little more detailed in your reply.
I need all the help I can get.

Thanks again
Maria

Maria

Consider using the DateDiff() function, with the "nn" (minutes) level of
precision. Are your In/Out fields formatted as Date/Time? If so, an
actual
Date AND Time are being entered.

Good luck

Jeff Boyce
<Access MVP>

I am creating a report based on a query that subtracts a persons Login
Time
from their Log Out Time: Currently the query has this expression
Hours: TimeValue([import]![Logout Time])-TimeValue([Login Time])
This works fine if the person logs in and logs out the same day. But
if
the
person logs in at 9:31 pm and logs off at 12:43 am (the next day but
the
same
shift) I am getting a result of 20:48 hours. I need to have a function
that
will handle the after midnight logouts so the total work hours won't be
skewed in the report that I create.

Thanks

 

[Jeff Boyce]

My question was about the format that ACCESS uses to store the data.

Jeff Boyce
<Access MVP>

Jeff,
Initally the log times are exported from an Avaya phone log into a text
format.
Example:
Agents Name 54744 6:02PM 6:48PM 4/28/2005
A macro imports the text file into an Excel document.
Example:
Agents Name 54744 6:02PM 12:15AM 4/29/2005

But occasionally the agents logs in one day and ends up logging off after
12
am the next day. The logoff needs to be counted as one shift but it ends
up
skewing the login hours for the next day:

To answer your question the Excel Spreadsheet is imported directly into a
table using the TransferSpreadsheet action. The format of the information
looks like above.

Thanks,

Maria

How Access displays (i.e., formats) a date/time field and how it stores
the
underlying value are not (necessarily) the same.

If you are importing values from Excel, what is the data type Access is
using for those values?

How are you importing (Get External Data | Import)? Or are you
"linking"?

Are you "parsing" the incoming data via a query or using it as is?

More info, please...

Jeff Boyce
<Access MVP>

Jeff,
Thanks for your reply but I am still stuck and need help.
When I import the times into the database from a speadsheet I don't
have a
date value in the field the time is imported in a military time format.
Example login time = 18:00 logout time = 0:23

I was thinking that creating a function within a module would be the
best
way to go. How do you call a function within a query?

I appreciate the help but can you be a little more detailed in your
reply.
I need all the help I can get.

Thanks again
Maria




:

Maria

Consider using the DateDiff() function, with the "nn" (minutes) level
of
precision. Are your In/Out fields formatted as Date/Time? If so, an
actual
Date AND Time are being entered.

Good luck

Jeff Boyce
<Access MVP>

I am creating a report based on a query that subtracts a persons
Login
Time
from their Log Out Time: Currently the query has this expression
Hours: TimeValue([import]![Logout Time])-TimeValue([Login Time])
This works fine if the person logs in and logs out the same day.
But
if
the
person logs in at 9:31 pm and logs off at 12:43 am (the next day but
the
same
shift) I am getting a result of 20:48 hours. I need to have a
function
that
will handle the after midnight logouts so the total work hours won't
be
skewed in the report that I create.

Thanks