Create Weekly Calendar from table of data

  • Thread starter Thread starter Daveo
  • Start date Start date
D

Daveo

Hi there,

I need to make an "employee tracker" style form where a user can select
a week ending date and then view the tracker for that week.

The data is stored in a table with the following fields:

ID
employee
project
startDate
endDate
obTravelDate
ibTravelDate
.... and a fair few others.

I can run a query that tells me who is where and when based on "Between
[Enter w/e date] and [Enter w/e date]-6) in the startDate field but I
have 2 main problems that I could do with some ideas on:

1 - What if the startDate and endDate both fall outwith the week that
the query looks at? The person is still away on a project and needs to
be represented on the tracker.
In the same vain, what if either the startDate or endDate falls outwith
the week I am looking at? - they dont show up in the query results.

2 - How do I represent it graphically in the form? I did fiddle about
using Duane Hookum's crew rotation schedule and had it working on a
report, but problem 1 above stumped me again. Is manipulating the size
of a text box with VBA the way forward, or should I put 7 unbound text
boxes on the form, who's values are somehow assigned at the time of
running the query using VBA?

I did look at a couple of off-the-shelf employee trackers, but I also
need to show things like which equipment they have with them from our
asset database, and they don't seem to be up to the job/customisable
enough.

Any help/pointers anyone can offer would be greatly appreciated.

Many thanks - David
 
I have a monthly scheduling module that displays a month calendar where
different data can be entered and shown each day. The calendar can also be
printed. Send me an email to (e-mail address removed) and I will send you a
screen shot.
 
Daveo said:
Hi there,

I need to make an "employee tracker" style form where a user can select
a week ending date and then view the tracker for that week.

The data is stored in a table with the following fields:

ID
employee
project
startDate
endDate
obTravelDate
ibTravelDate
... and a fair few others.

I can run a query that tells me who is where and when based on "Between
[Enter w/e date] and [Enter w/e date]-6) in the startDate field but I
have 2 main problems that I could do with some ideas on:

1 - What if the startDate and endDate both fall outwith the week that
the query looks at? The person is still away on a project and needs to
be represented on the tracker.
In the same vain, what if either the startDate or endDate falls outwith
the week I am looking at? - they dont show up in the query results.
Click to expand...

By tweaking the date values of both the employee record
and the report parameters.

So that
if the end date was in the past ignore record.
if the start date is in the future ignore record.
if the start date before start of week use start of week.
if the end date after end of week use end of week.

To find dates that fall in the desired range, the code to find
StartOfWeek, EndOfWeek is all over the place so not shown.
WHERE StartDate <= EndOfWeek() AND EndDate => StartOfWeek()

Always ensure that StartDate, EndDate are in the correct range.
WHERE Min(StartDate, EndDate) <= EndOfWeek() AND Max(StartDate, EndDate) =>
StartOfWeek()

To find the days to highlight.
FirstDay = Max(StartDate, StartOfWeek)
LastDay = Min(EndDate,EndOfWeek)

Always ensure that StartDate, EndDate are in the correct range.
Sub YourProcedure(StartDate As Date, EndDate As Date)
If IsNull(StartDate) Then
' Error Handling
Exit Sub
ElseIf IsNull(EndDate)Then
' Error Handling
Exit Sub
ElseIf StartDate > EndDate Then
' Reverse parameters
Call YourProcedure(EndDate, StartDate)

Exit Sub
End If
'-----------------------
' Your code here
'-----------------------
End Sub

Are we getting there? - John

<snip/>
 
Hi John,

Thanks for your help - I've got my head around what I need to ask the
query to do now. However, I can't get it to do it! When I write it in
SQL view, I get an error saying I'm using the wrong number of arguments
with the Min and Max functions. Should it be written purely in VBA?

Thanks - David
 
John,

I've got it now and have the form working as well. Thanks for all your
help. My headache is over!!.

David
 
Back
Top