Crew Rotation Modification - Weekly Gnatt Chart

[brownti via AccessMonster.com]

I started with Duane's crew rotation schedule and have modified it to show
only the current week using the SQL below. The problem i am running into is
that if StartDate is before monday of the current week or end date is after
friday of the current week, that information does not show up on the report.
How could adjust this SQL to break these entries up so that if the start date
is before monday, it would display it as a start on monday and the same thing
for end date? Thanks!

SELECT tblTrimmers.TrimmerID, [FirstName] & " " & [LastName] AS Name,
tblTrimmers.TrimmerColor, tblRotationII.UnitID, tblRotationII.StartDate,
tblRotationII.EndDate, [JobNumber] & " " & [UnitNumber] AS Unit
FROM tblJobInfo INNER JOIN (tblTrimmers INNER JOIN (tblRotationII INNER JOIN
tblUnitSelections ON tblRotationII.UnitID = tblUnitSelections.UnitID) ON
tblTrimmers.TrimmerID = tblRotationII.TrimmerID) ON tblJobInfo.JobID =
tblUnitSelections.JobID
WHERE (((tblRotationII.StartDate)>=Date()-Weekday(Date())+2) AND (
(tblRotationII.EndDate)<=Date()-Weekday(Date())+6));

 

[Marshall Barton]

I started with Duane's crew rotation schedule and have modified it to show
only the current week using the SQL below. The problem i am running into is
that if StartDate is before monday of the current week or end date is after
friday of the current week, that information does not show up on the report.
How could adjust this SQL to break these entries up so that if the start date
is before monday, it would display it as a start on monday and the same thing
for end date? Thanks!

SELECT tblTrimmers.TrimmerID, [FirstName] & " " & [LastName] AS Name,
tblTrimmers.TrimmerColor, tblRotationII.UnitID, tblRotationII.StartDate,
tblRotationII.EndDate, [JobNumber] & " " & [UnitNumber] AS Unit
FROM tblJobInfo INNER JOIN (tblTrimmers INNER JOIN (tblRotationII INNER JOIN
tblUnitSelections ON tblRotationII.UnitID = tblUnitSelections.UnitID) ON
tblTrimmers.TrimmerID = tblRotationII.TrimmerID) ON tblJobInfo.JobID =
tblUnitSelections.JobID
WHERE (((tblRotationII.StartDate)>=Date()-Weekday(Date())+2) AND (
(tblRotationII.EndDate)<=Date()-Weekday(Date())+6));

Maybe you want something more like air code:

SELECT tblTrimmers.TrimmerID,
[FirstName] & " " & [LastName] AS Name,
tblTrimmers.TrimmerColor, tblRotationII.UnitID,

IIf(tblRotationII.StartDate)>=(Date()-Weekday(Date())+2).

tblRotationII.StartDate,(Date()-Weekday(Date())+2)) As
AdjustedStart,

IIf(tblRotationII.EndDate)<=(Date()-Weekday(Date())+6)

tblRotationII.EndDate,(Date()-Weekday(Date())+6)) As
AdjustedEnd,
[JobNumber] & " " & [UnitNumber] AS Unit
FROM tblJobInfo
INNER JOIN (tblTrimmers
INNER JOIN (tblRotationII
INNER JOIN tblUnitSelections
ON tblRotationII.UnitID = tblUnitSelections.UnitID)
ON tblTrimmers.TrimmerID = tblRotationII.TrimmerID)
ON tblJobInfo.JobID = tblUnitSelections.JobID
WHERE tblRotationII.StartDate)<=(Date()-Weekday(Date())+6)
AND tblRotationII.EndDate)>=(Date()-Weekday(Date())+2)

 

[brownti via AccessMonster.com]

i'm not sure what you mean by "air code" but with a little modification i got
your code to work (i needed to replace a period with a comma, add a comma and
remove some parenthesis). Thanks a lot!

I started with Duane's crew rotation schedule and have modified it to show
only the current week using the SQL below. The problem i am running into is

[quoted text clipped - 13 lines]

WHERE (((tblRotationII.StartDate)>=Date()-Weekday(Date())+2) AND (
(tblRotationII.EndDate)<=Date()-Weekday(Date())+6));

Maybe you want something more like air code:

SELECT tblTrimmers.TrimmerID,
[FirstName] & " " & [LastName] AS Name,
tblTrimmers.TrimmerColor, tblRotationII.UnitID,

IIf(tblRotationII.StartDate)>=(Date()-Weekday(Date())+2).

tblRotationII.StartDate,(Date()-Weekday(Date())+2)) As
AdjustedStart,

IIf(tblRotationII.EndDate)<=(Date()-Weekday(Date())+6)

tblRotationII.EndDate,(Date()-Weekday(Date())+6)) As
AdjustedEnd,
[JobNumber] & " " & [UnitNumber] AS Unit
FROM tblJobInfo
INNER JOIN (tblTrimmers
INNER JOIN (tblRotationII
INNER JOIN tblUnitSelections
ON tblRotationII.UnitID = tblUnitSelections.UnitID)
ON tblTrimmers.TrimmerID = tblRotationII.TrimmerID)
ON tblJobInfo.JobID = tblUnitSelections.JobID
WHERE tblRotationII.StartDate)<=(Date()-Weekday(Date())+6)
AND tblRotationII.EndDate)>=(Date()-Weekday(Date())+2)

 

[John Spencer (MVP)]

AIR CODE = UNTESTED CODE that was typed directly into the window.

The term is analogous to AIR GUITAR - If you know what that is.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

i'm not sure what you mean by "air code" but with a little modification i got
your code to work (i needed to replace a period with a comma, add a comma and
remove some parenthesis). Thanks a lot!

I started with Duane's crew rotation schedule and have modified it to show
only the current week using the SQL below. The problem i am running into is

[quoted text clipped - 13 lines]

 

[brownti via AccessMonster.com]

Ah, i see. Well thank you. I have another problem that i dont know if you
will be able to help me with, but hopefully someone can. I am trying to
modify this report to use times as well as dates. The problem that i am
running into is that datediff is based on a 24 hour clock and we use a 10
hour workday. Below is the code that i am trying to get to work, the problem
is that i cant get half days (from 6 am to 12 pm) to display as half, they
display as a full day and their duration is 24 hours. Any help or ideas
would be appreciated. Thanks

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngDuration As Long 'days per job
Dim lngStart As Long 'start date of job
Dim lngTimeStart As Long 'hours for job
Dim lngAdjustedStart As Long
Dim lngAdjustedEnd As Long
Dim lngLMarg As Long
Dim dblFactor As Double
lngTimeStart = Forms!frmtrimmerschedule.cboStartDate
lngAdjustedStart = Me.StartDate
lngAdjustedEnd = Me.EndDate
'put a line control in your page header that starts monday and goes to
friday
lngLMarg = Me.boxTimeLine.Left
dblFactor = Me.boxTimeLine.Width / 120 'number of days in week times
Hours in a day
lngStart = DateDiff("h", lngTimeStart, lngAdjustedStart) 'dif from monday
of this week to start date
lngDuration = (DateDiff("n", lngAdjustedStart, lngAdjustedEnd)) / 60 'dif
of start date to end date
'set the color of the bar based on a data value
Me.txtName.BackColor = Me.VesselColor
Me.txtName.Width = 5 'avoid the positioning error
Me.txtName.Left = (lngStart * dblFactor) + lngLMarg
Me.txtName.Width = (lngDuration * dblFactor)
Me.MoveLayout = False
End Sub

AIR CODE = UNTESTED CODE that was typed directly into the window.

The term is analogous to AIR GUITAR - If you know what that is.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

i'm not sure what you mean by "air code" but with a little modification i got
your code to work (i needed to replace a period with a comma, add a comma and

[quoted text clipped - 3 lines]

only the current week using the SQL below. The problem i am running into is
[quoted text clipped - 13 lines]

 

[brownti via AccessMonster.com]

well i figured out one of my problems, i was dim 'ing as Long when i needed
the decimal, i solved that. But now still have the 24 hour vs 10 hour issue.
Here is my revised code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngDuration As Long 'days per job
Dim lngStart As Long 'start date of job
Dim lngTimeStart As Long 'hours for job
Dim lngAdjustedStart As Double
Dim lngAdjustedEnd As Double
Dim lngLMarg As Long
Dim dblFactor As Double
lngTimeStart = Forms!frmtrimmerschedule.cboStartDate
lngAdjustedStart = Me.StartDate
lngAdjustedEnd = Me.EndDate
'put a line control in your page header that starts monday and goes to
friday
lngLMarg = Me.boxTimeLine.Left
dblFactor = Me.boxTimeLine.Width / 120 'number of days in week times
Hours in a day
lngStart = DateDiff("h", lngTimeStart, lngAdjustedStart) 'dif from monday
of this week to start date
lngDuration = (DateDiff("h", lngAdjustedStart, lngAdjustedEnd)) 'dif of
start date to end date
MsgBox (lngAdjustedStart & " " & lngAdjustedEnd & " " & lngDuration)
'set the color of the bar based on a data value
Me.txtName.BackColor = Me.VesselColor
Me.txtName.Width = 5 'avoid the positioning error
Me.txtName.Left = (lngStart * dblFactor) + lngLMarg
Me.txtName.Width = (lngDuration * dblFactor)
Me.MoveLayout = False
End Sub

Ah, i see. Well thank you. I have another problem that i dont know if you
will be able to help me with, but hopefully someone can. I am trying to
modify this report to use times as well as dates. The problem that i am
running into is that datediff is based on a 24 hour clock and we use a 10
hour workday. Below is the code that i am trying to get to work, the problem
is that i cant get half days (from 6 am to 12 pm) to display as half, they
display as a full day and their duration is 24 hours. Any help or ideas
would be appreciated. Thanks

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngDuration As Long 'days per job
Dim lngStart As Long 'start date of job
Dim lngTimeStart As Long 'hours for job
Dim lngAdjustedStart As Long
Dim lngAdjustedEnd As Long
Dim lngLMarg As Long
Dim dblFactor As Double
lngTimeStart = Forms!frmtrimmerschedule.cboStartDate
lngAdjustedStart = Me.StartDate
lngAdjustedEnd = Me.EndDate
'put a line control in your page header that starts monday and goes to
friday
lngLMarg = Me.boxTimeLine.Left
dblFactor = Me.boxTimeLine.Width / 120 'number of days in week times
Hours in a day
lngStart = DateDiff("h", lngTimeStart, lngAdjustedStart) 'dif from monday
of this week to start date
lngDuration = (DateDiff("n", lngAdjustedStart, lngAdjustedEnd)) / 60 'dif
of start date to end date
'set the color of the bar based on a data value
Me.txtName.BackColor = Me.VesselColor
Me.txtName.Width = 5 'avoid the positioning error
Me.txtName.Left = (lngStart * dblFactor) + lngLMarg
Me.txtName.Width = (lngDuration * dblFactor)
Me.MoveLayout = False
End Sub

AIR CODE = UNTESTED CODE that was typed directly into the window.

[quoted text clipped - 10 lines]

only the current week using the SQL below. The problem i am running into is
[quoted text clipped - 13 lines]

 

[Duane Hookom]

I'm not sure how you expect to display 10 hrs vs 24 hrs. Is your timeline not
continuous? Do you want to 'cut out' sections of hours in the timeline?

--
Duane Hookom
Microsoft Access MVP

well i figured out one of my problems, i was dim 'ing as Long when i needed
the decimal, i solved that. But now still have the 24 hour vs 10 hour issue.
Here is my revised code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngDuration As Long 'days per job
Dim lngStart As Long 'start date of job
Dim lngTimeStart As Long 'hours for job
Dim lngAdjustedStart As Double
Dim lngAdjustedEnd As Double
Dim lngLMarg As Long
Dim dblFactor As Double
lngTimeStart = Forms!frmtrimmerschedule.cboStartDate
lngAdjustedStart = Me.StartDate
lngAdjustedEnd = Me.EndDate
'put a line control in your page header that starts monday and goes to
friday
lngLMarg = Me.boxTimeLine.Left
dblFactor = Me.boxTimeLine.Width / 120 'number of days in week times
Hours in a day
lngStart = DateDiff("h", lngTimeStart, lngAdjustedStart) 'dif from monday
of this week to start date
lngDuration = (DateDiff("h", lngAdjustedStart, lngAdjustedEnd)) 'dif of
start date to end date
MsgBox (lngAdjustedStart & " " & lngAdjustedEnd & " " & lngDuration)
'set the color of the bar based on a data value
Me.txtName.BackColor = Me.VesselColor
Me.txtName.Width = 5 'avoid the positioning error
Me.txtName.Left = (lngStart * dblFactor) + lngLMarg
Me.txtName.Width = (lngDuration * dblFactor)
Me.MoveLayout = False
End Sub

Ah, i see. Well thank you. I have another problem that i dont know if you
will be able to help me with, but hopefully someone can. I am trying to
modify this report to use times as well as dates. The problem that i am
running into is that datediff is based on a 24 hour clock and we use a 10
hour workday. Below is the code that i am trying to get to work, the problem
is that i cant get half days (from 6 am to 12 pm) to display as half, they
display as a full day and their duration is 24 hours. Any help or ideas
would be appreciated. Thanks

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngDuration As Long 'days per job
Dim lngStart As Long 'start date of job
Dim lngTimeStart As Long 'hours for job
Dim lngAdjustedStart As Long
Dim lngAdjustedEnd As Long
Dim lngLMarg As Long
Dim dblFactor As Double
lngTimeStart = Forms!frmtrimmerschedule.cboStartDate
lngAdjustedStart = Me.StartDate
lngAdjustedEnd = Me.EndDate
'put a line control in your page header that starts monday and goes to
friday
lngLMarg = Me.boxTimeLine.Left
dblFactor = Me.boxTimeLine.Width / 120 'number of days in week times
Hours in a day
lngStart = DateDiff("h", lngTimeStart, lngAdjustedStart) 'dif from monday
of this week to start date
lngDuration = (DateDiff("n", lngAdjustedStart, lngAdjustedEnd)) / 60 'dif
of start date to end date
'set the color of the bar based on a data value
Me.txtName.BackColor = Me.VesselColor
Me.txtName.Width = 5 'avoid the positioning error
Me.txtName.Left = (lngStart * dblFactor) + lngLMarg
Me.txtName.Width = (lngDuration * dblFactor)
Me.MoveLayout = False
End Sub

AIR CODE = UNTESTED CODE that was typed directly into the window.

[quoted text clipped - 10 lines]

only the current week using the SQL below. The problem i am running into is
[quoted text clipped - 13 lines]

 

[brownti via AccessMonster.com]

Yes i would like to cutout sections. We only work from 6am to 4pm so there
is often a blank space between 4pm and 6am the next day. so although someone
is scheduled from 6am on the first until 4pm on the third, they arent really
working after 4 on the first and second but it shows that they are. I hope
that makes sense...

I'm not sure how you expect to display 10 hrs vs 24 hrs. Is your timeline not
continuous? Do you want to 'cut out' sections of hours in the timeline?

well i figured out one of my problems, i was dim 'ing as Long when i needed
the decimal, i solved that. But now still have the 24 hour vs 10 hour issue.

[quoted text clipped - 71 lines]

only the current week using the SQL below. The problem i am running into is
[quoted text clipped - 13 lines]

 

[Duane Hookom]

I think you would need to determine which date in the week (1st, 2nd, 3rd,..)
and subtract a number of twips per date. This would move stuff to the left.
Assuming the twips normally between 4 PM and 6 AM is 2000, you would subtract
2000 twips from the display of the second days events, 4000 from the 3rd days
etc.

--
Duane Hookom
Microsoft Access MVP

Yes i would like to cutout sections. We only work from 6am to 4pm so there
is often a blank space between 4pm and 6am the next day. so although someone
is scheduled from 6am on the first until 4pm on the third, they arent really
working after 4 on the first and second but it shows that they are. I hope
that makes sense...

I'm not sure how you expect to display 10 hrs vs 24 hrs. Is your timeline not
continuous? Do you want to 'cut out' sections of hours in the timeline?

well i figured out one of my problems, i was dim 'ing as Long when i needed
the decimal, i solved that. But now still have the 24 hour vs 10 hour issue.

[quoted text clipped - 71 lines]

only the current week using the SQL below. The problem i am running into is
[quoted text clipped - 13 lines]

 

[brownti via AccessMonster.com]

hmm...that doesnt sound very feasible, i think i will just stick with looking
at the 24 hour clock.

I think you would need to determine which date in the week (1st, 2nd, 3rd,..)
and subtract a number of twips per date. This would move stuff to the left.
Assuming the twips normally between 4 PM and 6 AM is 2000, you would subtract
2000 twips from the display of the second days events, 4000 from the 3rd days
etc.

Yes i would like to cutout sections. We only work from 6am to 4pm so there
is often a blank space between 4pm and 6am the next day. so although someone

[quoted text clipped - 10 lines]

only the current week using the SQL below. The problem i am running into is
[quoted text clipped - 13 lines]

 

[Duane Hookom]

It's feasible but would take a bunch of math and testing.

--
Duane Hookom
Microsoft Access MVP

hmm...that doesnt sound very feasible, i think i will just stick with looking
at the 24 hour clock.

I think you would need to determine which date in the week (1st, 2nd, 3rd,..)
and subtract a number of twips per date. This would move stuff to the left.
Assuming the twips normally between 4 PM and 6 AM is 2000, you would subtract
2000 twips from the display of the second days events, 4000 from the 3rd days
etc.

Yes i would like to cutout sections. We only work from 6am to 4pm so there
is often a blank space between 4pm and 6am the next day. so although someone

[quoted text clipped - 10 lines]

only the current week using the SQL below. The problem i am running into is
[quoted text clipped - 13 lines]

 

[brownti via AccessMonster.com]

I think i will see if people will be alright with using it as a 24 hour day
and if not look into trying something else. Thanks for your help.

It's feasible but would take a bunch of math and testing.

hmm...that doesnt sound very feasible, i think i will just stick with looking
at the 24 hour clock.

[quoted text clipped - 10 lines]

only the current week using the SQL below. The problem i am running into is
[quoted text clipped - 13 lines]