Time query help

[JimUK]

I have a report that I have been using the DateAdd function to query the last 12 hours for a helpdesk log and that works no problem. Our shifts run from 7am to 7pm (days) and 7pm to 7am (nights). What I would like to do is make a query that would look at the time when generated and only grab log files that go back to 7am or pm. For instance, it's 12 noon and I try to print a log of the day shift so far, I want it to go and grab logs from 12 noon only back to 7am that day. I'm lost on how to start this. Do I need 2 constants (7am or 7pm) and then figure hours back to one of those times depending on when I run query? Or can this even be done......Help!

 

[dhookom]

Do you have a table name, a field name, and some sample records? We don't know if you have a single field for date and time or two fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JimUK]

Duane,

Thanks for the response. Table name is tblWOLog and their are 2 time fields, DateofEntry and WOClosedDate.
Other fields that I'm pulling to make the log entry are, Problem, EmployeeName, Actiontaken and WOStatus.
I check both the date fields in my query as there are 2 types of entries made: an actual workorder on a particular piece of equipment that has a problem and a generic log entry such as a phone call that we helped someone setup a Windows XP account.
There are situations where the bosses want to see the WOCloseDate on equipment problems vice just the Dateof Entry on a normal log entry.
Hope that clears it up some.
Thanks in advance.

Jim

 

[dhookom]

and some sample records? We don't know if you have a single field for date and time or two fields.

It isn't clear if DateofEntry contains both a date and time since you state

their are 2 time fields

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JimUK]

Whoops sorry!

They both contain date and time in the format
12/02/2005 13:00:00

Example:

Date of Entry: 12/02/2005 11:10:30

Date of Closure: 12/02/2005 14:00:00

 

[dhookom]

You might be able to set the criteria under your date/time field to something like:
DateAdd("h",-5,Int(DateAdd("h",5,Now())*2)/2)

I have tested this thoroughly but maybe you can get the basics. Since you are needing to divide a date into two, I double the Now(), find the integer part and then divide by 2. Then you have to account for the 7:00 since it's not 12:00.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JimUK]

Duane,

Thanks for the input. I'll start with that and see where it leads me.

Jim