Time comparison in a query

[rae]

Hi all. Question:
I want to run three queries that add up all the time spent on a project for
one day, one week and one month.
This is the problem. I can get total time for one query specially when I am
doing so for one run, but what I can't figure out is how to make the query
'gather' todays date and then the date one week ago or one month ago ect.
without having the user enter dates.
I'm a newbie and need simple or easy to follow directions.

 

[John Vinson]

Hi all. Question:
I want to run three queries that add up all the time spent on a project for
one day, one week and one month.
This is the problem. I can get total time for one query specially when I am
doing so for one run, but what I can't figure out is how to make the query
'gather' todays date and then the date one week ago or one month ago ect.
without having the user enter dates.
I'm a newbie and need simple or easy to follow directions.

Without knowing the structure or contents of the table, it's a bit
hard to be sure... but assuming that you have a Date/Time field named
EventTime recording the date and time at which an event occured, you
can use

= Date()

as a criterion in a query to get all records where this field occurred
since midnight;

= DateAdd("d", -7, Date())

to get all records where the event occurred since midnight one week
ago today; and

= DateAdd("m", -1, Date())

for all since a month ago today.

John W. Vinson[MVP]

 

[rae]

John,
Thanks so much. I'm going to try this out tomorrow. Yes, I do have a date
field. so, I would do:
date>=dateadd("d", -7, Date())
in this fashion?
Seems so easy. I couldn't find the answer in any of my sql books.
Will let you know how it turns out!

 

[John Vinson]

John,
Thanks so much. I'm going to try this out tomorrow. Yes, I do have a date
field. so, I would do:
date>=dateadd("d", -7, Date())
in this fashion?

Yes... but DON'T use "date" as a fieldname. It's a reserved word; if
you use it - especially in a query also referencing the Date()
function - it's very likely that Access will get confused. Rename the
field, or at the very least use [date] with square brackets.

In the query grid you'ld just put

= DateAdd("d", -7, Date())

on the Criteria line under the field, without repeating the fieldname
in the criterion.

John W. Vinson[MVP]

 

[rae]

Hello John!
Thank you very much for that bit of information. It works just fine...
However, now I have another question. I'll ask it here but if I need to
start a new thread, please just let me know.

I have the following query:
SELECT Table1.Program, Table1.worker,
HoursAndMinutes(Sum(Table1.checkout-Table1.checkin)) AS Total_Time_Spent,
Table1.Appt_Date
FROM Table1
GROUP BY Table1.worker, Table1.Appt_Date, Table1.Program
HAVING (((Table1.Appt_Date)>=DateAdd("d",-120,Date())));

This works fine, however what I really want to do is see the total time
spent per person on each project over the space of a month. What I get is the
total time spent on each project each day.

Program worker Total_Time_Spent Appt_Date
WIA Davxxx 0:15 8/31/2006
WIA Davxxx 0:30 10/14/2006
TANF Fulxxx 0:15 11/6/2006

I don't care if there is an Appt_Date field, however, because I use it to
define the dates, I need it in the script...yes? I don't want to see the
worker name repeated unless they are working on several projects.
What could I be doing wrong?

Rae

John,
Thanks so much. I'm going to try this out tomorrow. Yes, I do have a date
field. so, I would do:
date>=dateadd("d", -7, Date())
in this fashion?

Yes... but DON'T use "date" as a fieldname. It's a reserved word; if
you use it - especially in a query also referencing the Date()
function - it's very likely that Access will get confused. Rename the
field, or at the very least use [date] with square brackets.

In the query grid you'ld just put

= DateAdd("d", -7, Date())

on the Criteria line under the field, without repeating the fieldname
in the criterion.

John W. Vinson[MVP]