Start and End Date trim.

[jon6035]

I found code from Duane Hookum to create a time line between two dates. It was initially set for a year view and would display who was scheduled to be the Captain of ship between dates.

I adapted it to fit 34 days and got it to work from a specific date 9/1/2011. However, when I have a date that exceeds the shifting control form I receive an error that "The control or subform control is too large for this location" because a date of 8/1/2011 grows a textbox control to far to the left.

I need to have a query that will search StartDate and EndDate and then trim those dates to fit the criteria entered. Is this even possible?

Event 2 has a StartDate of 8/1/2011 and an EndDate of 10/15/2011



i.e. Between #9/1/2011# and #9/30/2011# would return StartDate:9/1/2011 EndDate:9/30/2011 for Event 2.

The "Copy of RptRotation" in the attached file is giving the error. Because of the value 8/1/2011 in the query above, found in selTEEP.

[June7]

I tried running the report. It errors on: Me.txtName.Left = (lngStart * dblFactor) + lngLMarg
It is trying to set textbox txtName Left -31
I did a fix then it errored on the width so did another fix. These are the lines I fixed just to get it to run.

Me.txtName.Left = (IIf(lngStart < 0, 0, lngStart) * dblFactor) + lngLMarg
Me.txtName.Width = (IIf(lngDuration > 34, 34, lngDuration) * dblFactor)

Nothing seems to happen with boxTimeLine. Is anything supposed to be done with it, don't see any code for it.

[jon6035]

Well, first off. Thank you for the quick reply. I have been search forums for answers and still haven't found anything.

I took your code and put it my report and it works fine now on the left side of the report. On the right side, if I have a date that is greater than 10/5/2011 I get the same error.

I can't understand enough of what you did to make it work on the right side, like you did on the left side.

Oh, and the boxTimeLine is just for setting the width of the growing field. Near as I can tell the VBA takes the value and applies it to the timeline box. In the original example it wrapped around 12 boxes for each month and was divided by 365.

I added your code the attached, and set the dates to receive the error again.

Any help would be greatly appreciated.

[June7]

As I understand this report, it will produce one page to show schedule for a maximum of 34 days. In this example 9/1/2011 through 10/4/2011. Since 10/5/2011 exceeds the max date I did a fix and tried setup for August and it errored on the Left again. So did this fix:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim lngDuration As Long 'days of tour
    Dim lngStart As Long  'start date of tour
    Dim lngLMarg As Long
    Dim dblFactor As Double
    lngLMarg = Me.boxTimeLine.Left
    dblFactor = Me.boxTimeLine.Width
    lngStart = DateDiff("d", #9/1/2011#, Me.[StartDate])
    lngDuration = DateDiff("d", Me.[StartDate], Me.[EndDate] + 1)
    'set the color of the bar based on a data value
    Me.txtName.BackColor = Me.colorvalue
    Me.txtName.Width = 0 'avoid the positioning error
    Me.txtName.Visible = False
    If Me!EndDate > Me.txtDay1 And Me!StartDate < Me.txtDay1 + 34 Then
        Me.txtName.Visible = True
        Me.txtName.Left = IIf(lngStart < 0, 0, lngStart) * dblFactor + lngLMarg
        Me.txtName.Width = IIf(lngStart + lngDuration > 34, 34 - IIf(lngStart < 0, 0, lngStart), lngDuration) * dblFactor
    End If
    Me.MoveLayout = False
End Sub

I now see that the boxTimeLine is used only to set variables in the code. These could be hard coded and the box eliminated. Or just set the box as not visible or no outline so it doesn't show on report.

BTW, this code is calculating Left and Width measures in twips. Although you can see inch measures in Access properties, VBA recognizes twips. See http://www.pbdr.com/vbtips/gen/convtwip.htm

[jon6035]

So, first off thank you. It seems to run fine except when I get dates to close to each other. (I can't seem to fully understand when it errors out ) I added the version with dates it is erroring on.

I only chose 34, because that was how many days (text boxes) I could fit on the page. I did not have any correlation to days in a month.

My intent is to pass the start date to the report from a parameter on a form. So that I could start the query on 9/17/2011 or any other date. and have it display. I use it to identify "white space".

The other item that I have been pondering on all day is that perhaps a query could limit the results to the days that exist in the range. i.e. given a start date of 9/1/2011 + 34, the query returns the event name and the greatest date and least date that exists for that event,within the given date range. Does that even make sense?

I was thinking that this could then be used for the report record source. Problem is, I have no idea how to do it.

So in the attached document, I got rid of all the other tables/queries and made my original query a table. I put a not next to the record that when I move the date ealier in time, I start receiving the error again on the left side.

Not sure if this is possible at this stage. All of your help has been greatly appreciated.

[June7]

I downloaded your revised project and ran the report. The code does not error. What data caused error for you?

One of the colorvalues is black so text does not show on the bar. Oops, even when I change the color, the event name not showing on bar for event 8. I changed person name so bar is on a separate line and then the event name shows.

I would stay away from those dark colors, especially red as it gets too much attention. Try: 15714765, 10210715, 11398133

Not sure what else I can do with this.

[jon6035]

I appreciate your time on this. When you change the date for Event 1 to 8/6/2011 it errors out.

What do you think of the idea of having a query restrict the start and end dates to the parameter dates?

Also, I forgot to mention that I appreciate the information on TWIPS and the recommendation for different colors.

So I made some queries that change the start and end dates for the event that matches what I input.

selInclusiveDates

SELECT tblDates.EventDates, Events.PKEvents
FROM tblDates, Events
WHERE (((tblDates.EventDates)>=[Events].[StartDate] And (tblDates.EventDates)<=[Events].[EndDate]));

selNewStartandEndTEEP

SELECT selInclusiveTEEPDates.PKEvents, Events.EventName, Events.StartDate, Events.EndDate, selInclusiveTEEPDates.EventDates
FROM selInclusiveTEEPDates INNER JOIN Events ON selInclusiveTEEPDates.PKEvents = Events.PKEvents
GROUP BY selInclusiveTEEPDates.PKEvents, Events.EventName, Events.StartDate, Events.EndDate, selInclusiveTEEPDates.EventDates
HAVING (((selInclusiveTEEPDates.EventDates) Between #9/1/2011# And #9/1/2011#+33));

I will pass the start date for the query/report from a form at run time. This will allow me to dynamically change everything as required.


selCalculatedStartEnd

SELECT selNewStartEndTEEP.PKEvents, selNewStartEndTEEP.EventName, Min(selNewStartEndTEEP.EventDates) AS CalculatedStart, Max(selNewStartEndTEEP.EventDates) AS CalculatedEnd, selTEEP.[Personnel Name], selTEEP.SectionLU, selTEEP.colorvalue, selTEEP.TrainingType, selTEEP.PKMMEventtoPersonnel, selTEEP.PKEvents, selTEEP.Year
FROM selTEEP INNER JOIN selNewStartEndTEEP ON selTEEP.PKEvents = selNewStartEndTEEP.PKEvents
GROUP BY selNewStartEndTEEP.PKEvents, selNewStartEndTEEP.EventName, selTEEP.[Personnel Name], selTEEP.SectionLU, selTEEP.colorvalue, selTEEP.TrainingType, selTEEP.PKMMEventtoPersonnel, selTEEP.PKEvents, selTEEP.Year;


I launch the report from a form with a text box defaulted to Date(). I can change the date if I want to see something before.

I attached a pdf with the report. Thank you for all your help.

Jon

[June7]

Those reports do look nice.

I did some testing and the bars display with issues if multiple events for same person and the events extend beyond the report period.

As for your select query, I think it excludes records where the start or end is within the report period but the start or end date is not. In other words, it will report personnel schedules only where both the start and end of event are within the report period. If that's sufficient then you have success.

I tested with the 8/6/2011 date and yes did error. Needed one more fix, have to handle negative lngStart:
Me.txtName.Width = IIf(IIf(lngStart < 0, 0, lngStart) + lngDuration > 34, 34 - IIf(lngStart < 0, 0, lngStart), lngDuration) * dblFactor