Auto Fill Date

[Broadbonian]

I am using 2 active x calendar controls on a form. The user clicks on a
begin date 6/8/08 and an end date 6/14/08. I would like my report to display
this as column headers.
Sun 8,Jun Mon 9,Jun Tues 10, June Ect.... thru Sat 14, June

Can anyone help with this?
Darc

 

[fredg]

I am using 2 active x calendar controls on a form. The user clicks on a
begin date 6/8/08 and an end date 6/14/08. I would like my report to display
this as column headers.
Sun 8,Jun Mon 9,Jun Tues 10, June Ect.... thru Sat 14, June

Can anyone help with this?
Darc

Column headers in the Page Header section?

Add a Form to your database.
Include an unbound control set to a valid date format.
Name this control StartDate.
Add another control as above.
Name this control EndDate.
Add your Calendar control to the form.
Code the Calendar Control's Double-click event to fill the selected
dates into the StartDate control and the EndDate control:

Private Sub objCalendar_DblClick()
Dim ctl As Control
Set ctl = Me!objCalendar
If IsNull([StartDate]) Then
[StartDate] = ctl.Value
Else
[EndDate] = ctl.Value
End If
End Sub

Add a command button to this form.
Code it's Click event:
Me.Visible = False

Next add labels to the Page Header of your report.
Name them, in left to right order, "Label1", "Label2", etc., "Label7"
Set each of these Label control's Tag property to X (No quotes)

Code the Report's Open Event:
DoCmd.OpenForm "frmDates", , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "frmDates"

Code the Report's Report Header Format event:

Dim C As Label
' Clear the old captions
For Each C In Me.Section(3).Controls
If C.Tag = "X" Then
C.Caption = ""
End If
Next C

' Fill in New Captions
Dim SDate As Date
Dim EDate As Date
Dim intX As Integer
SDate = Forms!frmDates!StartDate
EDate = Forms!frmDates!EndDate
For intX = 1 To DateDiff("d", SDate, EDate) + 1
Me("Label" & intX).Caption = Format(SDate, "ddd, dd mmm")
SDate = SDate + 1
Next intX

You should add additional code to the above to limit the number of
days selected to that number of labels you have set up in the Page
header, or less.
Also, I've left any error handling up to you.

 

[Broadbonian]

Thanks so much. I'll try this tomorrow
--
Darc

I am using 2 active x calendar controls on a form. The user clicks on a
begin date 6/8/08 and an end date 6/14/08. I would like my report to display
this as column headers.
Sun 8,Jun Mon 9,Jun Tues 10, June Ect.... thru Sat 14, June

Can anyone help with this?
Darc

Column headers in the Page Header section?

Add a Form to your database.
Include an unbound control set to a valid date format.
Name this control StartDate.
Add another control as above.
Name this control EndDate.
Add your Calendar control to the form.
Code the Calendar Control's Double-click event to fill the selected
dates into the StartDate control and the EndDate control:

Private Sub objCalendar_DblClick()
Dim ctl As Control
Set ctl = Me!objCalendar
If IsNull([StartDate]) Then
[StartDate] = ctl.Value
Else
[EndDate] = ctl.Value
End If
End Sub

Add a command button to this form.
Code it's Click event:
Me.Visible = False

Next add labels to the Page Header of your report.
Name them, in left to right order, "Label1", "Label2", etc., "Label7"
Set each of these Label control's Tag property to X (No quotes)

Code the Report's Open Event:
DoCmd.OpenForm "frmDates", , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "frmDates"

Code the Report's Report Header Format event:

Dim C As Label
' Clear the old captions
For Each C In Me.Section(3).Controls
If C.Tag = "X" Then
C.Caption = ""
End If
Next C

' Fill in New Captions
Dim SDate As Date
Dim EDate As Date
Dim intX As Integer
SDate = Forms!frmDates!StartDate
EDate = Forms!frmDates!EndDate
For intX = 1 To DateDiff("d", SDate, EDate) + 1
Me("Label" & intX).Caption = Format(SDate, "ddd, dd mmm")
SDate = SDate + 1
Next intX

You should add additional code to the above to limit the number of
days selected to that number of labels you have set up in the Page
header, or less.
Also, I've left any error handling up to you.

 

[Broadbonian]

Hi Fred, Thanks for your help. I am to the point of testing the report,
however is is looking for label 8. I don't know how to write the additional
code to stop it at just 7 labels. Can you help again? Thank You Darc
--
Darc

I am using 2 active x calendar controls on a form. The user clicks on a
begin date 6/8/08 and an end date 6/14/08. I would like my report to display
this as column headers.
Sun 8,Jun Mon 9,Jun Tues 10, June Ect.... thru Sat 14, June

Can anyone help with this?
Darc

Column headers in the Page Header section?

Add a Form to your database.
Include an unbound control set to a valid date format.
Name this control StartDate.
Add another control as above.
Name this control EndDate.
Add your Calendar control to the form.
Code the Calendar Control's Double-click event to fill the selected
dates into the StartDate control and the EndDate control:

Private Sub objCalendar_DblClick()
Dim ctl As Control
Set ctl = Me!objCalendar
If IsNull([StartDate]) Then
[StartDate] = ctl.Value
Else
[EndDate] = ctl.Value
End If
End Sub

Add a command button to this form.
Code it's Click event:
Me.Visible = False

Next add labels to the Page Header of your report.
Name them, in left to right order, "Label1", "Label2", etc., "Label7"
Set each of these Label control's Tag property to X (No quotes)

Code the Report's Open Event:
DoCmd.OpenForm "frmDates", , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "frmDates"

Code the Report's Report Header Format event:

Dim C As Label
' Clear the old captions
For Each C In Me.Section(3).Controls
If C.Tag = "X" Then
C.Caption = ""
End If
Next C

' Fill in New Captions
Dim SDate As Date
Dim EDate As Date
Dim intX As Integer
SDate = Forms!frmDates!StartDate
EDate = Forms!frmDates!EndDate
For intX = 1 To DateDiff("d", SDate, EDate) + 1
Me("Label" & intX).Caption = Format(SDate, "ddd, dd mmm")
SDate = SDate + 1
Next intX

You should add additional code to the above to limit the number of
days selected to that number of labels you have set up in the Page
header, or less.
Also, I've left any error handling up to you.

 

[fredg]

Hi Fred, Thanks for your help. I am to the point of testing the report,
however is is looking for label 8. I don't know how to write the additional
code to stop it at just 7 labels. Can you help again? Thank You Darc

That code works well for me.

How many of these labels did you add? 7?
Did you place an X in the Tag property of each of these labels?
Did you name them Label1, Label2 .... Labe7?
Did you select 7 days from the calendar ... June1 - June7?
Or did you select June1 -June8?

Do you always want exactly 7 labels?
If so just change
For intX = 1 To DateDiff("d", SDate, EDate) + 1
to
For intX = 1 To 7
and any last date past teh 7th one you chose will be ignored.

However, if the number of labels can vary you'll need to step through
your code and see what values you are coming up with.
Open the code window. Place a breakpoint on the
If IsNull([StartDate]) Then line.
Then step through the code when you run the report and the code
breaks (press F8 for each line).
When you get down to For intX = 1 To DateDiff("d", SDate, EDate) + 1
after moving to the next line hover your cursor over intX. It should
read 1 the first time, incrementing to 7 after the last time.

 

[Broadbonian]

Hi Fred, I added your new code, it didn't work at first. I had the labels in
section 2, page header. I changed the code to read section 2, and it worked
just like you said it would. Never would have been able to get this done
myself, appreciate all your help.
--
Darc

Hi Fred, Thanks for your help. I am to the point of testing the report,
however is is looking for label 8. I don't know how to write the additional
code to stop it at just 7 labels. Can you help again? Thank You Darc

That code works well for me.

How many of these labels did you add? 7?
Did you place an X in the Tag property of each of these labels?
Did you name them Label1, Label2 .... Labe7?
Did you select 7 days from the calendar ... June1 - June7?
Or did you select June1 -June8?

Do you always want exactly 7 labels?
If so just change
For intX = 1 To DateDiff("d", SDate, EDate) + 1
to
For intX = 1 To 7
and any last date past teh 7th one you chose will be ignored.

However, if the number of labels can vary you'll need to step through
your code and see what values you are coming up with.
Open the code window. Place a breakpoint on the
If IsNull([StartDate]) Then line.
Then step through the code when you run the report and the code
breaks (press F8 for each line).
When you get down to For intX = 1 To DateDiff("d", SDate, EDate) + 1
after moving to the next line hover your cursor over intX. It should
read 1 the first time, incrementing to 7 after the last time.

 

[Broadbonian]

Hi John, I have been reading & trying to do this today. Can you help once
again?
I would like to color labels in the detail section based on detail data
compared to the labels you created for me.
Sun 8,Jun Mon 9,Jun Tue
10,Jun Wed 11,Jun
Emp-Jim,Bob 6/9-12/08 RED RED
RED
Trk:#1, #2 6/11/08
BLUE
Equip:966, D5Cat 6/9-10/08 GRN GRN

Emp text box name-tbEmpStDt and tbEmpEnDt

Appreciate any help. Thanks Darc


--
Darc

I am using 2 active x calendar controls on a form. The user clicks on a
begin date 6/8/08 and an end date 6/14/08. I would like my report to display
this as column headers.
Sun 8,Jun Mon 9,Jun Tues 10, June Ect.... thru Sat 14, June

Can anyone help with this?
Darc

Column headers in the Page Header section?

Add a Form to your database.
Include an unbound control set to a valid date format.
Name this control StartDate.
Add another control as above.
Name this control EndDate.
Add your Calendar control to the form.
Code the Calendar Control's Double-click event to fill the selected
dates into the StartDate control and the EndDate control:

Private Sub objCalendar_DblClick()
Dim ctl As Control
Set ctl = Me!objCalendar
If IsNull([StartDate]) Then
[StartDate] = ctl.Value
Else
[EndDate] = ctl.Value
End If
End Sub

Add a command button to this form.
Code it's Click event:
Me.Visible = False

Next add labels to the Page Header of your report.
Name them, in left to right order, "Label1", "Label2", etc., "Label7"
Set each of these Label control's Tag property to X (No quotes)

Code the Report's Open Event:
DoCmd.OpenForm "frmDates", , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "frmDates"

Code the Report's Report Header Format event:

Dim C As Label
' Clear the old captions
For Each C In Me.Section(3).Controls
If C.Tag = "X" Then
C.Caption = ""
End If
Next C

' Fill in New Captions
Dim SDate As Date
Dim EDate As Date
Dim intX As Integer
SDate = Forms!frmDates!StartDate
EDate = Forms!frmDates!EndDate
For intX = 1 To DateDiff("d", SDate, EDate) + 1
Me("Label" & intX).Caption = Format(SDate, "ddd, dd mmm")
SDate = SDate + 1
Next intX

You should add additional code to the above to limit the number of
days selected to that number of labels you have set up in the Page
header, or less.
Also, I've left any error handling up to you.

 

[Broadbonian]

Just realized I called you John. So Sorry....

Darc

Hi John, I have been reading & trying to do this today. Can you help once
again?
I would like to color labels in the detail section based on detail data
compared to the labels you created for me.
Sun 8,Jun Mon 9,Jun Tue
10,Jun Wed 11,Jun
Emp-Jim,Bob 6/9-12/08 RED RED
RED
Trk:#1, #2 6/11/08
BLUE
Equip:966, D5Cat 6/9-10/08 GRN GRN

Emp text box name-tbEmpStDt and tbEmpEnDt

Appreciate any help. Thanks Darc


--
Darc

I am using 2 active x calendar controls on a form. The user clicks on a
begin date 6/8/08 and an end date 6/14/08. I would like my report to display
this as column headers.
Sun 8,Jun Mon 9,Jun Tues 10, June Ect.... thru Sat 14, June

Can anyone help with this?
Darc

Column headers in the Page Header section?

Add a Form to your database.
Include an unbound control set to a valid date format.
Name this control StartDate.
Add another control as above.
Name this control EndDate.
Add your Calendar control to the form.
Code the Calendar Control's Double-click event to fill the selected
dates into the StartDate control and the EndDate control:

Private Sub objCalendar_DblClick()
Dim ctl As Control
Set ctl = Me!objCalendar
If IsNull([StartDate]) Then
[StartDate] = ctl.Value
Else
[EndDate] = ctl.Value
End If
End Sub

Add a command button to this form.
Code it's Click event:
Me.Visible = False

Next add labels to the Page Header of your report.
Name them, in left to right order, "Label1", "Label2", etc., "Label7"
Set each of these Label control's Tag property to X (No quotes)

Code the Report's Open Event:
DoCmd.OpenForm "frmDates", , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "frmDates"

Code the Report's Report Header Format event:

Dim C As Label
' Clear the old captions
For Each C In Me.Section(3).Controls
If C.Tag = "X" Then
C.Caption = ""
End If
Next C

' Fill in New Captions
Dim SDate As Date
Dim EDate As Date
Dim intX As Integer
SDate = Forms!frmDates!StartDate
EDate = Forms!frmDates!EndDate
For intX = 1 To DateDiff("d", SDate, EDate) + 1
Me("Label" & intX).Caption = Format(SDate, "ddd, dd mmm")
SDate = SDate + 1
Next intX

You should add additional code to the above to limit the number of
days selected to that number of labels you have set up in the Page
header, or less.
Also, I've left any error handling up to you.