Calender report conversion.

[Simeon Cheeseman]

Hi there.

I got a sample database with several reports from the forums
(http://www.access.hookom.net/Samples.htm.) and I am trying to convert one to
work with days months instead of days and hours. I am using the 'weekly
schedule by doctor'. My converted code is as follows:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim lngTopMargin As Long

Dim lngOneMinute As Long 'size of one minute in twips

Dim datSchedStart As Date

Dim strdate As String

Dim startdate As Date

Dim enddate As Date

Dim ttest As String



startdate = Me.Month & "/" & Me.Start & "/" & Me.Year

enddate = Me.Month & "/" & Me.End & "/" & Me.Year



strdate = "1/1/" & Me.Year & ""

datSchedStart = strdate

lngOneMinute = 12 'number of twips in one minute

lngTopMargin = 720 'timeline starts 1/2" down in section



ttest = datediff("d", datSchedStart, startdate)



Me.MoveLayout = False

Me.child_id.Top = lngTopMargin + ttest * lngOneMinute

Me.child_id.Height = datediff("d", startdate, enddate) * lngOneMinute

Me.child_id.Left = 2160

'DateDiff("m", Me.Month, Me.Month) *

End Sub



And this is my replacement SQL query:

SELECT DatePart("m",[date_booked_start]) AS [Month],
DatePart("d",[date_booked_start]) AS Start, tbl_bookings.house,
DatePart("d",[date_booked_end]) AS [End],
DateAdd("m",-Month([Month]),[Month])+1 AS WeekOf, tbl_bookings.child_id,
tbl_house.reportcolumn, DatePart("yyyy",[date_booked_start]) AS [Year]
FROM tbl_house INNER JOIN (tbl_details INNER JOIN tbl_bookings ON
tbl_details.child_id = tbl_bookings.child_id) ON tbl_house.house_name =
tbl_bookings.house;


Thanks Simeon.

 

[Duane Hookom]

What do you want across the top and down the left? I would not create dates
from strings. Consider

startdate = DateSerial( Me.Year,Me.Month, Me.Start)
enddate = DateSerial( Me.Year,Me.Month, Me.End)
strdate = DateSerial( Me.Year,1,1)

Go from there and come back with specific issues with results.

 

[Simeon Cheeseman]

Across the top I want months and across the left I want days.

using your suggested dates I ran the report, on opening the report All I
could see was a number on the left and 2 empty boxes at the top, on
attempting to go to the last page access hung whilst formatting.

thanks again, simeon

What do you want across the top and down the left? I would not create dates
from strings. Consider

startdate = DateSerial( Me.Year,Me.Month, Me.Start)
enddate = DateSerial( Me.Year,Me.Month, Me.End)
strdate = DateSerial( Me.Year,1,1)

Go from there and come back with specific issues with results.
--
Duane Hookom
Microsoft Access MVP

Hi there.

I got a sample database with several reports from the forums
(http://www.access.hookom.net/Samples.htm.) and I am trying to convert one to
work with days months instead of days and hours. I am using the 'weekly
schedule by doctor'. My converted code is as follows:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim lngTopMargin As Long

Dim lngOneMinute As Long 'size of one minute in twips

Dim datSchedStart As Date

Dim strdate As String

Dim startdate As Date

Dim enddate As Date

Dim ttest As String



startdate = Me.Month & "/" & Me.Start & "/" & Me.Year

enddate = Me.Month & "/" & Me.End & "/" & Me.Year



strdate = "1/1/" & Me.Year & ""

datSchedStart = strdate

lngOneMinute = 12 'number of twips in one minute

lngTopMargin = 720 'timeline starts 1/2" down in section



ttest = datediff("d", datSchedStart, startdate)



Me.MoveLayout = False

Me.child_id.Top = lngTopMargin + ttest * lngOneMinute

Me.child_id.Height = datediff("d", startdate, enddate) * lngOneMinute

Me.child_id.Left = 2160

'DateDiff("m", Me.Month, Me.Month) *

End Sub



And this is my replacement SQL query:

SELECT DatePart("m",[date_booked_start]) AS [Month],
DatePart("d",[date_booked_start]) AS Start, tbl_bookings.house,
DatePart("d",[date_booked_end]) AS [End],
DateAdd("m",-Month([Month]),[Month])+1 AS WeekOf, tbl_bookings.child_id,
tbl_house.reportcolumn, DatePart("yyyy",[date_booked_start]) AS [Year]
FROM tbl_house INNER JOIN (tbl_details INNER JOIN tbl_bookings ON
tbl_details.child_id = tbl_bookings.child_id) ON tbl_house.house_name =
tbl_bookings.house;


Thanks Simeon.

 

[Duane Hookom]

If you want months across the top you need to set the properties
Left: (month - beginning month) * Column Separation.
Top: (day of month) * (height of section)/31
Height: Duration of days in the Month * (height of section)/31

--
Duane Hookom
Microsoft Access MVP

Across the top I want months and across the left I want days.

using your suggested dates I ran the report, on opening the report All I
could see was a number on the left and 2 empty boxes at the top, on
attempting to go to the last page access hung whilst formatting.

thanks again, simeon

What do you want across the top and down the left? I would not create dates
from strings. Consider

startdate = DateSerial( Me.Year,Me.Month, Me.Start)
enddate = DateSerial( Me.Year,Me.Month, Me.End)
strdate = DateSerial( Me.Year,1,1)

Go from there and come back with specific issues with results.
--
Duane Hookom
Microsoft Access MVP

Hi there.

I got a sample database with several reports from the forums
(http://www.access.hookom.net/Samples.htm.) and I am trying to convert one to
work with days months instead of days and hours. I am using the 'weekly
schedule by doctor'. My converted code is as follows:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim lngTopMargin As Long

Dim lngOneMinute As Long 'size of one minute in twips

Dim datSchedStart As Date

Dim strdate As String

Dim startdate As Date

Dim enddate As Date

Dim ttest As String



startdate = Me.Month & "/" & Me.Start & "/" & Me.Year

enddate = Me.Month & "/" & Me.End & "/" & Me.Year



strdate = "1/1/" & Me.Year & ""

datSchedStart = strdate

lngOneMinute = 12 'number of twips in one minute

lngTopMargin = 720 'timeline starts 1/2" down in section



ttest = datediff("d", datSchedStart, startdate)



Me.MoveLayout = False

Me.child_id.Top = lngTopMargin + ttest * lngOneMinute

Me.child_id.Height = datediff("d", startdate, enddate) * lngOneMinute

Me.child_id.Left = 2160

'DateDiff("m", Me.Month, Me.Month) *

End Sub



And this is my replacement SQL query:

SELECT DatePart("m",[date_booked_start]) AS [Month],
DatePart("d",[date_booked_start]) AS Start, tbl_bookings.house,
DatePart("d",[date_booked_end]) AS [End],
DateAdd("m",-Month([Month]),[Month])+1 AS WeekOf, tbl_bookings.child_id,
tbl_house.reportcolumn, DatePart("yyyy",[date_booked_start]) AS [Year]
FROM tbl_house INNER JOIN (tbl_details INNER JOIN tbl_bookings ON
tbl_details.child_id = tbl_bookings.child_id) ON tbl_house.house_name =
tbl_bookings.house;


Thanks Simeon.

 

[Simeon Cheeseman]

OK, I have altered my code and it now reads:

startdate = DateSerial(Me.Year, Me.Month, Me.Start)
enddate = DateSerial(Me.Year, Me.Month, Me.End)
strdate = DateSerial(Me.Year, 1, 1)

datSchedStart = strdate
lngOneMinute = 7200 / 31 'number of twips in one minute
lngTopMargin = 720 'timeline starts 1/2" down in section

ttest = Me.Start 'datediff("d", datSchedStart, startdate)

Me.MoveLayout = False
Me.child_id.Top = lngTopMargin + ttest * lngOneMinute
Me.child_id.Height = datediff("d", startdate, enddate) * lngOneMinute
Me.child_id.Left = (Me.Month - 1) * 2160

I now have problems with the fact that all my dates seem to be on the same
column, the headers aren't there and the measure up the side isn't there
either, also it seems to create an excessive amount of pages still.

thanks simeon.

If you want months across the top you need to set the properties
Left: (month - beginning month) * Column Separation.
Top: (day of month) * (height of section)/31
Height: Duration of days in the Month * (height of section)/31

--
Duane Hookom
Microsoft Access MVP

Across the top I want months and across the left I want days.

using your suggested dates I ran the report, on opening the report All I
could see was a number on the left and 2 empty boxes at the top, on
attempting to go to the last page access hung whilst formatting.

thanks again, simeon

What do you want across the top and down the left? I would not create dates
from strings. Consider

startdate = DateSerial( Me.Year,Me.Month, Me.Start)
enddate = DateSerial( Me.Year,Me.Month, Me.End)
strdate = DateSerial( Me.Year,1,1)

Go from there and come back with specific issues with results.
--
Duane Hookom
Microsoft Access MVP


:

Hi there.

I got a sample database with several reports from the forums
(http://www.access.hookom.net/Samples.htm.) and I am trying to convert one to
work with days months instead of days and hours. I am using the 'weekly
schedule by doctor'. My converted code is as follows:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim lngTopMargin As Long

Dim lngOneMinute As Long 'size of one minute in twips

Dim datSchedStart As Date

Dim strdate As String

Dim startdate As Date

Dim enddate As Date

Dim ttest As String



startdate = Me.Month & "/" & Me.Start & "/" & Me.Year

enddate = Me.Month & "/" & Me.End & "/" & Me.Year



strdate = "1/1/" & Me.Year & ""

datSchedStart = strdate

lngOneMinute = 12 'number of twips in one minute

lngTopMargin = 720 'timeline starts 1/2" down in section



ttest = datediff("d", datSchedStart, startdate)



Me.MoveLayout = False

Me.child_id.Top = lngTopMargin + ttest * lngOneMinute

Me.child_id.Height = datediff("d", startdate, enddate) * lngOneMinute

Me.child_id.Left = 2160

'DateDiff("m", Me.Month, Me.Month) *

End Sub



And this is my replacement SQL query:

SELECT DatePart("m",[date_booked_start]) AS [Month],
DatePart("d",[date_booked_start]) AS Start, tbl_bookings.house,
DatePart("d",[date_booked_end]) AS [End],
DateAdd("m",-Month([Month]),[Month])+1 AS WeekOf, tbl_bookings.child_id,
tbl_house.reportcolumn, DatePart("yyyy",[date_booked_start]) AS [Year]
FROM tbl_house INNER JOIN (tbl_details INNER JOIN tbl_bookings ON
tbl_details.child_id = tbl_bookings.child_id) ON tbl_house.house_name =
tbl_bookings.house;


Thanks Simeon.

 

[Duane Hookom]

It's a bit difficult to troubleshoot without seeing values. Do you know how
to set and use breakpoints? How about debug.print to view values?

What section of the report is this code running?

--
Duane Hookom
Microsoft Access MVP

OK, I have altered my code and it now reads:

startdate = DateSerial(Me.Year, Me.Month, Me.Start)
enddate = DateSerial(Me.Year, Me.Month, Me.End)
strdate = DateSerial(Me.Year, 1, 1)

datSchedStart = strdate
lngOneMinute = 7200 / 31 'number of twips in one minute
lngTopMargin = 720 'timeline starts 1/2" down in section

ttest = Me.Start 'datediff("d", datSchedStart, startdate)

Me.MoveLayout = False
Me.child_id.Top = lngTopMargin + ttest * lngOneMinute
Me.child_id.Height = datediff("d", startdate, enddate) * lngOneMinute
Me.child_id.Left = (Me.Month - 1) * 2160

I now have problems with the fact that all my dates seem to be on the same
column, the headers aren't there and the measure up the side isn't there
either, also it seems to create an excessive amount of pages still.

thanks simeon.

If you want months across the top you need to set the properties
Left: (month - beginning month) * Column Separation.
Top: (day of month) * (height of section)/31
Height: Duration of days in the Month * (height of section)/31

--
Duane Hookom
Microsoft Access MVP

Across the top I want months and across the left I want days.

using your suggested dates I ran the report, on opening the report All I
could see was a number on the left and 2 empty boxes at the top, on
attempting to go to the last page access hung whilst formatting.

thanks again, simeon


:

What do you want across the top and down the left? I would not create dates
from strings. Consider

startdate = DateSerial( Me.Year,Me.Month, Me.Start)
enddate = DateSerial( Me.Year,Me.Month, Me.End)
strdate = DateSerial( Me.Year,1,1)

Go from there and come back with specific issues with results.
--
Duane Hookom
Microsoft Access MVP


:

Hi there.

I got a sample database with several reports from the forums
(http://www.access.hookom.net/Samples.htm.) and I am trying to convert one to
work with days months instead of days and hours. I am using the 'weekly
schedule by doctor'. My converted code is as follows:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim lngTopMargin As Long

Dim lngOneMinute As Long 'size of one minute in twips

Dim datSchedStart As Date

Dim strdate As String

Dim startdate As Date

Dim enddate As Date

Dim ttest As String



startdate = Me.Month & "/" & Me.Start & "/" & Me.Year

enddate = Me.Month & "/" & Me.End & "/" & Me.Year



strdate = "1/1/" & Me.Year & ""

datSchedStart = strdate

lngOneMinute = 12 'number of twips in one minute

lngTopMargin = 720 'timeline starts 1/2" down in section



ttest = datediff("d", datSchedStart, startdate)



Me.MoveLayout = False

Me.child_id.Top = lngTopMargin + ttest * lngOneMinute

Me.child_id.Height = datediff("d", startdate, enddate) * lngOneMinute

Me.child_id.Left = 2160

'DateDiff("m", Me.Month, Me.Month) *

End Sub



And this is my replacement SQL query:

SELECT DatePart("m",[date_booked_start]) AS [Month],
DatePart("d",[date_booked_start]) AS Start, tbl_bookings.house,
DatePart("d",[date_booked_end]) AS [End],
DateAdd("m",-Month([Month]),[Month])+1 AS WeekOf, tbl_bookings.child_id,
tbl_house.reportcolumn, DatePart("yyyy",[date_booked_start]) AS [Year]
FROM tbl_house INNER JOIN (tbl_details INNER JOIN tbl_bookings ON
tbl_details.child_id = tbl_bookings.child_id) ON tbl_house.house_name =
tbl_bookings.house;


Thanks Simeon.

 

[Simeon Cheeseman]

I do know how to use breakpoints and things like that, but not debug.print,
ive seen it mentioned but not known how it works.

As far as I know the code runs "On Open" and all the places are the same as
in the origional report.

Thanks simeon.

It's a bit difficult to troubleshoot without seeing values. Do you know how
to set and use breakpoints? How about debug.print to view values?

What section of the report is this code running?

--
Duane Hookom
Microsoft Access MVP

OK, I have altered my code and it now reads:

startdate = DateSerial(Me.Year, Me.Month, Me.Start)
enddate = DateSerial(Me.Year, Me.Month, Me.End)
strdate = DateSerial(Me.Year, 1, 1)

datSchedStart = strdate
lngOneMinute = 7200 / 31 'number of twips in one minute
lngTopMargin = 720 'timeline starts 1/2" down in section

ttest = Me.Start 'datediff("d", datSchedStart, startdate)

Me.MoveLayout = False
Me.child_id.Top = lngTopMargin + ttest * lngOneMinute
Me.child_id.Height = datediff("d", startdate, enddate) * lngOneMinute
Me.child_id.Left = (Me.Month - 1) * 2160

I now have problems with the fact that all my dates seem to be on the same
column, the headers aren't there and the measure up the side isn't there
either, also it seems to create an excessive amount of pages still.

thanks simeon.

If you want months across the top you need to set the properties
Left: (month - beginning month) * Column Separation.
Top: (day of month) * (height of section)/31
Height: Duration of days in the Month * (height of section)/31

--
Duane Hookom
Microsoft Access MVP


:

Across the top I want months and across the left I want days.

using your suggested dates I ran the report, on opening the report All I
could see was a number on the left and 2 empty boxes at the top, on
attempting to go to the last page access hung whilst formatting.

thanks again, simeon


:

What do you want across the top and down the left? I would not create dates
from strings. Consider

startdate = DateSerial( Me.Year,Me.Month, Me.Start)
enddate = DateSerial( Me.Year,Me.Month, Me.End)
strdate = DateSerial( Me.Year,1,1)

Go from there and come back with specific issues with results.
--
Duane Hookom
Microsoft Access MVP


:

Hi there.

I got a sample database with several reports from the forums
(http://www.access.hookom.net/Samples.htm.) and I am trying to convert one to
work with days months instead of days and hours. I am using the 'weekly
schedule by doctor'. My converted code is as follows:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim lngTopMargin As Long

Dim lngOneMinute As Long 'size of one minute in twips

Dim datSchedStart As Date

Dim strdate As String

Dim startdate As Date

Dim enddate As Date

Dim ttest As String



startdate = Me.Month & "/" & Me.Start & "/" & Me.Year

enddate = Me.Month & "/" & Me.End & "/" & Me.Year



strdate = "1/1/" & Me.Year & ""

datSchedStart = strdate

lngOneMinute = 12 'number of twips in one minute

lngTopMargin = 720 'timeline starts 1/2" down in section



ttest = datediff("d", datSchedStart, startdate)



Me.MoveLayout = False

Me.child_id.Top = lngTopMargin + ttest * lngOneMinute

Me.child_id.Height = datediff("d", startdate, enddate) * lngOneMinute

Me.child_id.Left = 2160

'DateDiff("m", Me.Month, Me.Month) *

End Sub



And this is my replacement SQL query:

SELECT DatePart("m",[date_booked_start]) AS [Month],
DatePart("d",[date_booked_start]) AS Start, tbl_bookings.house,
DatePart("d",[date_booked_end]) AS [End],
DateAdd("m",-Month([Month]),[Month])+1 AS WeekOf, tbl_bookings.child_id,
tbl_house.reportcolumn, DatePart("yyyy",[date_booked_start]) AS [Year]
FROM tbl_house INNER JOIN (tbl_details INNER JOIN tbl_bookings ON
tbl_details.child_id = tbl_bookings.child_id) ON tbl_house.house_name =
tbl_bookings.house;


Thanks Simeon.

 

[Duane Hookom]

The "On Open" event had only one line of code
DoCmd.Maximize
What do you see when you step through the code following the break point?
You should be able to place your mouse over your code to read the current
values. I can't see these but you can.

--
Duane Hookom
Microsoft Access MVP

I do know how to use breakpoints and things like that, but not debug.print,
ive seen it mentioned but not known how it works.

As far as I know the code runs "On Open" and all the places are the same as
in the origional report.

Thanks simeon.

It's a bit difficult to troubleshoot without seeing values. Do you know how
to set and use breakpoints? How about debug.print to view values?

What section of the report is this code running?

--
Duane Hookom
Microsoft Access MVP

OK, I have altered my code and it now reads:

startdate = DateSerial(Me.Year, Me.Month, Me.Start)
enddate = DateSerial(Me.Year, Me.Month, Me.End)
strdate = DateSerial(Me.Year, 1, 1)

datSchedStart = strdate
lngOneMinute = 7200 / 31 'number of twips in one minute
lngTopMargin = 720 'timeline starts 1/2" down in section

ttest = Me.Start 'datediff("d", datSchedStart, startdate)

Me.MoveLayout = False
Me.child_id.Top = lngTopMargin + ttest * lngOneMinute
Me.child_id.Height = datediff("d", startdate, enddate) * lngOneMinute
Me.child_id.Left = (Me.Month - 1) * 2160

I now have problems with the fact that all my dates seem to be on the same
column, the headers aren't there and the measure up the side isn't there
either, also it seems to create an excessive amount of pages still.

thanks simeon.

:

If you want months across the top you need to set the properties
Left: (month - beginning month) * Column Separation.
Top: (day of month) * (height of section)/31
Height: Duration of days in the Month * (height of section)/31

--
Duane Hookom
Microsoft Access MVP


:

Across the top I want months and across the left I want days.

using your suggested dates I ran the report, on opening the report All I
could see was a number on the left and 2 empty boxes at the top, on
attempting to go to the last page access hung whilst formatting.

thanks again, simeon


:

What do you want across the top and down the left? I would not create dates
from strings. Consider

startdate = DateSerial( Me.Year,Me.Month, Me.Start)
enddate = DateSerial( Me.Year,Me.Month, Me.End)
strdate = DateSerial( Me.Year,1,1)

Go from there and come back with specific issues with results.
--
Duane Hookom
Microsoft Access MVP


:

Hi there.

I got a sample database with several reports from the forums
(http://www.access.hookom.net/Samples.htm.) and I am trying to convert one to
work with days months instead of days and hours. I am using the 'weekly
schedule by doctor'. My converted code is as follows:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim lngTopMargin As Long

Dim lngOneMinute As Long 'size of one minute in twips

Dim datSchedStart As Date

Dim strdate As String

Dim startdate As Date

Dim enddate As Date

Dim ttest As String



startdate = Me.Month & "/" & Me.Start & "/" & Me.Year

enddate = Me.Month & "/" & Me.End & "/" & Me.Year



strdate = "1/1/" & Me.Year & ""

datSchedStart = strdate

lngOneMinute = 12 'number of twips in one minute

lngTopMargin = 720 'timeline starts 1/2" down in section



ttest = datediff("d", datSchedStart, startdate)



Me.MoveLayout = False

Me.child_id.Top = lngTopMargin + ttest * lngOneMinute

Me.child_id.Height = datediff("d", startdate, enddate) * lngOneMinute

Me.child_id.Left = 2160

'DateDiff("m", Me.Month, Me.Month) *

End Sub



And this is my replacement SQL query:

SELECT DatePart("m",[date_booked_start]) AS [Month],
DatePart("d",[date_booked_start]) AS Start, tbl_bookings.house,
DatePart("d",[date_booked_end]) AS [End],
DateAdd("m",-Month([Month]),[Month])+1 AS WeekOf, tbl_bookings.child_id,
tbl_house.reportcolumn, DatePart("yyyy",[date_booked_start]) AS [Year]
FROM tbl_house INNER JOIN (tbl_details INNER JOIN tbl_bookings ON
tbl_details.child_id = tbl_bookings.child_id) ON tbl_house.house_name =
tbl_bookings.house;


Thanks Simeon.

 

[Simeon Cheeseman]

Sorry, my code is in the Detail_Format section.

I just updated windows which seems to help, i now have the scale on the left
and the headers.

At the end of one section the values are: Child_id.top = 6752
child_id.left = 2160 (result of taking out the "-1" in 'me.month - 1')
child_id.height = 464

if i attempt to go to the last page, or any page except the first I get the
error, Run-time error '2100' : The control or subform control is too large
for this location.

The "On Open" event had only one line of code
DoCmd.Maximize
What do you see when you step through the code following the break point?
You should be able to place your mouse over your code to read the current
values. I can't see these but you can.

--
Duane Hookom
Microsoft Access MVP

I do know how to use breakpoints and things like that, but not debug.print,
ive seen it mentioned but not known how it works.

As far as I know the code runs "On Open" and all the places are the same as
in the origional report.

Thanks simeon.

It's a bit difficult to troubleshoot without seeing values. Do you know how
to set and use breakpoints? How about debug.print to view values?

What section of the report is this code running?

--
Duane Hookom
Microsoft Access MVP


:

OK, I have altered my code and it now reads:

startdate = DateSerial(Me.Year, Me.Month, Me.Start)
enddate = DateSerial(Me.Year, Me.Month, Me.End)
strdate = DateSerial(Me.Year, 1, 1)

datSchedStart = strdate
lngOneMinute = 7200 / 31 'number of twips in one minute
lngTopMargin = 720 'timeline starts 1/2" down in section

ttest = Me.Start 'datediff("d", datSchedStart, startdate)

Me.MoveLayout = False
Me.child_id.Top = lngTopMargin + ttest * lngOneMinute
Me.child_id.Height = datediff("d", startdate, enddate) * lngOneMinute
Me.child_id.Left = (Me.Month - 1) * 2160

I now have problems with the fact that all my dates seem to be on the same
column, the headers aren't there and the measure up the side isn't there
either, also it seems to create an excessive amount of pages still.

thanks simeon.

:

If you want months across the top you need to set the properties
Left: (month - beginning month) * Column Separation.
Top: (day of month) * (height of section)/31
Height: Duration of days in the Month * (height of section)/31

--
Duane Hookom
Microsoft Access MVP


:

Across the top I want months and across the left I want days.

using your suggested dates I ran the report, on opening the report All I
could see was a number on the left and 2 empty boxes at the top, on
attempting to go to the last page access hung whilst formatting.

thanks again, simeon


:

What do you want across the top and down the left? I would not create dates
from strings. Consider

startdate = DateSerial( Me.Year,Me.Month, Me.Start)
enddate = DateSerial( Me.Year,Me.Month, Me.End)
strdate = DateSerial( Me.Year,1,1)

Go from there and come back with specific issues with results.
--
Duane Hookom
Microsoft Access MVP


:

Hi there.

I got a sample database with several reports from the forums
(http://www.access.hookom.net/Samples.htm.) and I am trying to convert one to
work with days months instead of days and hours. I am using the 'weekly
schedule by doctor'. My converted code is as follows:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim lngTopMargin As Long

Dim lngOneMinute As Long 'size of one minute in twips

Dim datSchedStart As Date

Dim strdate As String

Dim startdate As Date

Dim enddate As Date

Dim ttest As String



startdate = Me.Month & "/" & Me.Start & "/" & Me.Year

enddate = Me.Month & "/" & Me.End & "/" & Me.Year



strdate = "1/1/" & Me.Year & ""

datSchedStart = strdate

lngOneMinute = 12 'number of twips in one minute

lngTopMargin = 720 'timeline starts 1/2" down in section



ttest = datediff("d", datSchedStart, startdate)



Me.MoveLayout = False

Me.child_id.Top = lngTopMargin + ttest * lngOneMinute

Me.child_id.Height = datediff("d", startdate, enddate) * lngOneMinute

Me.child_id.Left = 2160

'DateDiff("m", Me.Month, Me.Month) *

End Sub



And this is my replacement SQL query:

SELECT DatePart("m",[date_booked_start]) AS [Month],
DatePart("d",[date_booked_start]) AS Start, tbl_bookings.house,
DatePart("d",[date_booked_end]) AS [End],
DateAdd("m",-Month([Month]),[Month])+1 AS WeekOf, tbl_bookings.child_id,
tbl_house.reportcolumn, DatePart("yyyy",[date_booked_start]) AS [Year]
FROM tbl_house INNER JOIN (tbl_details INNER JOIN tbl_bookings ON
tbl_details.child_id = tbl_bookings.child_id) ON tbl_house.house_name =
tbl_bookings.house;


Thanks Simeon.

 

[Duane Hookom]

I found that I had to set my control heights to a very low number prior to
setting the top property. You may also be attempting to set the left property
too high based on the month and the width of the control.

All of this must be configured using the method that I used in creating the
original.
Trial and error stepping through code to view results.
--
Duane Hookom
Microsoft Access MVP

Sorry, my code is in the Detail_Format section.

I just updated windows which seems to help, i now have the scale on the left
and the headers.

At the end of one section the values are: Child_id.top = 6752
child_id.left = 2160 (result of taking out the "-1" in 'me.month - 1')
child_id.height = 464

if i attempt to go to the last page, or any page except the first I get the
error, Run-time error '2100' : The control or subform control is too large
for this location.

The "On Open" event had only one line of code
DoCmd.Maximize
What do you see when you step through the code following the break point?
You should be able to place your mouse over your code to read the current
values. I can't see these but you can.

--
Duane Hookom
Microsoft Access MVP

I do know how to use breakpoints and things like that, but not debug.print,
ive seen it mentioned but not known how it works.

As far as I know the code runs "On Open" and all the places are the same as
in the origional report.

Thanks simeon.

:

It's a bit difficult to troubleshoot without seeing values. Do you know how
to set and use breakpoints? How about debug.print to view values?

What section of the report is this code running?

--
Duane Hookom
Microsoft Access MVP


:

OK, I have altered my code and it now reads:

startdate = DateSerial(Me.Year, Me.Month, Me.Start)
enddate = DateSerial(Me.Year, Me.Month, Me.End)
strdate = DateSerial(Me.Year, 1, 1)

datSchedStart = strdate
lngOneMinute = 7200 / 31 'number of twips in one minute
lngTopMargin = 720 'timeline starts 1/2" down in section

ttest = Me.Start 'datediff("d", datSchedStart, startdate)

Me.MoveLayout = False
Me.child_id.Top = lngTopMargin + ttest * lngOneMinute
Me.child_id.Height = datediff("d", startdate, enddate) * lngOneMinute
Me.child_id.Left = (Me.Month - 1) * 2160

I now have problems with the fact that all my dates seem to be on the same
column, the headers aren't there and the measure up the side isn't there
either, also it seems to create an excessive amount of pages still.

thanks simeon.

:

If you want months across the top you need to set the properties
Left: (month - beginning month) * Column Separation.
Top: (day of month) * (height of section)/31
Height: Duration of days in the Month * (height of section)/31

--
Duane Hookom
Microsoft Access MVP


:

Across the top I want months and across the left I want days.

using your suggested dates I ran the report, on opening the report All I
could see was a number on the left and 2 empty boxes at the top, on
attempting to go to the last page access hung whilst formatting.

thanks again, simeon


:

What do you want across the top and down the left? I would not create dates
from strings. Consider

startdate = DateSerial( Me.Year,Me.Month, Me.Start)
enddate = DateSerial( Me.Year,Me.Month, Me.End)
strdate = DateSerial( Me.Year,1,1)

Go from there and come back with specific issues with results.
--
Duane Hookom
Microsoft Access MVP


:

Hi there.

I got a sample database with several reports from the forums
(http://www.access.hookom.net/Samples.htm.) and I am trying to convert one to
work with days months instead of days and hours. I am using the 'weekly
schedule by doctor'. My converted code is as follows:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim lngTopMargin As Long

Dim lngOneMinute As Long 'size of one minute in twips

Dim datSchedStart As Date

Dim strdate As String

Dim startdate As Date

Dim enddate As Date

Dim ttest As String



startdate = Me.Month & "/" & Me.Start & "/" & Me.Year

enddate = Me.Month & "/" & Me.End & "/" & Me.Year



strdate = "1/1/" & Me.Year & ""

datSchedStart = strdate

lngOneMinute = 12 'number of twips in one minute

lngTopMargin = 720 'timeline starts 1/2" down in section



ttest = datediff("d", datSchedStart, startdate)



Me.MoveLayout = False

Me.child_id.Top = lngTopMargin + ttest * lngOneMinute

Me.child_id.Height = datediff("d", startdate, enddate) * lngOneMinute

Me.child_id.Left = 2160

'DateDiff("m", Me.Month, Me.Month) *

End Sub



And this is my replacement SQL query:

SELECT DatePart("m",[date_booked_start]) AS [Month],
DatePart("d",[date_booked_start]) AS Start, tbl_bookings.house,
DatePart("d",[date_booked_end]) AS [End],
DateAdd("m",-Month([Month]),[Month])+1 AS WeekOf, tbl_bookings.child_id,
tbl_house.reportcolumn, DatePart("yyyy",[date_booked_start]) AS [Year]
FROM tbl_house INNER JOIN (tbl_details INNER JOIN tbl_bookings ON
tbl_details.child_id = tbl_bookings.child_id) ON tbl_house.house_name =
tbl_bookings.house;


Thanks Simeon.