Viewing all data entered for the week

[Guest]

Here's my situation...
I have about 20 pieces of equipment that must be maintained. We do 'weekly
checks' on all of them. I have built a database that has tables for each
piece of equipment, and gives me reports on deficiencies, error messages, and
the like (regardless of date).
However, sometimes we get cought up in work, and one guy will do some checks
on Monday, and another will finish up on Tuesday or Wednesday (or both). I'm
looking to be able to pull up a report with all data entries for the week,
regardless of what day they were entered.
Currently, the date field autopopulates and is hidden from the guys. Is
there a simple way to do this? I considered a second field in each table
that lists the monday of that week or even the week number based on the
'today's date' field, but I don't know how to do it!
Any help would be great. I am at the 'beginner' level...shocked I've made
it this far!

Thanks in advance,
Kim

 

[Guest]

Here are two functions. One returns the first day of the week for the date
you pass to it and the other returns the last day. It is on a Monday to
Sunday basis. So if you enter today's date (9/5/2007) WeekStart Date will
return 9/3/2007 and WeekEndDate will return 9/9/2007

PUblic Function WeekEndDate(ByVal BaseDate As Date) As Date
'Dave Hargis 9/04
'Returns the last date of the accounting week for the date entered)

WeekEndDate = DateAdd("d", 7 + vbSunday - DatePart("w", BaseDate),
BaseDate)
End Function

PUblic Function WeekStartDate(ByVal BaseDate As Date) As Date
'Dave Hargis 9/04
'Returns the first date of the accounting week for the date entered)

WeekStartDate = DateAdd("d", vbSunday - DatePart("w", BaseDate,
vbMonday), BaseDate)
End Function

 

[Michael Gramelspacher]

Here's my situation...
I have about 20 pieces of equipment that must be maintained. We do 'weekly
checks' on all of them. I have built a database that has tables for each
piece of equipment, and gives me reports on deficiencies, error messages, and
the like (regardless of date).
However, sometimes we get cought up in work, and one guy will do some checks
on Monday, and another will finish up on Tuesday or Wednesday (or both). I'm
looking to be able to pull up a report with all data entries for the week,
regardless of what day they were entered.
Currently, the date field autopopulates and is hidden from the guys. Is
there a simple way to do this? I considered a second field in each table
that lists the monday of that week or even the week number based on the
'today's date' field, but I don't know how to do it!
Any help would be great. I am at the 'beginner' level...shocked I've made
it this far!

Thanks in advance,
Kim

MAybe just:

SELECT * FROM VehicleMaintenance WHERE DatePart("ww",service_date) =
datepart("ww",Date())-1

This should give you all vehicles serviced last week. Try it and see,
but use your real table and date column names.

 

[Michael Gramelspacher]

SELECT * FROM VehicleMaintenance WHERE DatePart("ww",service_date) =
datepart("ww",Date())-1

that will not work, try this

SELECT * FROM VehicleMaintenance WHERE (((service_date) Between
(Date()-DatePart("w",Date())-7) And (Date()-DatePart("w",Date())-1)));

If run today, this returns dates between 25 - 31 Aug 2007.

 

[Guest]

Great idea, Michael, but the problem may be that the OP wants to use a
business week rather than a calendar week. If it were a calendar week, I
would use your code.

 

[Guest]

Okay. I need it to be by work week. However, where do I put that code? I'm
assuming in the table somewhere...

 

[Michael Gramelspacher]

Okay. I need it to be by work week. However, where do I put that code? I'm
assuming in the table somewhere...

So what is your definition of work week? And which week do you want
data for? The present week, last week, or some other week? Code does
not go in a table. An SQL statement extracts data from a table. Did
you edit the query and run it?