Hi All,
Greetings,
How to conditionally format all the records in a repot for certain period (eg. for 14 days) before the date() of the report.
Thanks,
VKKT
Conditional Date Format in Report for certain period
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional Date Format in Report for certain period
Do you want to format text boxes and combo boxes on the report, or do you want to change the background color of the detail section? Or something else?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Conditional Date Format in Report for certain period
Sorry Hans, I am always coming with half information.
Actually I want to highlight the background color of the detailed section. Eg. highlight the records for all the 14 days before and the date printed the report
Regards,
VKKT.
Actually I want to highlight the background color of the detailed section. Eg. highlight the records for all the 14 days before and the date printed the report
Regards,
VKKT.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional Date Format in Report for certain period
OK, let's say this will depend on a field named DateField.
Open the report in design view.
You may want to set the Back Style of the controls in the Detail section to Transparent, but that's up to you.
Create an On Format event procedure for the Detail section:
Remark: you will only see the result in Print Preview, not in Report View, because the On Format event doesn't run in Report View.
Open the report in design view.
You may want to set the Back Style of the controls in the Detail section to Transparent, but that's up to you.
Create an On Format event procedure for the Detail section:
Code: Select all
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.DateField >= Date - 14 And Me.Datefield < Date Then
Me.Detail.BackColor = RGB(192, 192, 192)
Me.Detail.AlternateBackColor = RGB(192, 192, 192)
Else
Me.Detail.BackColor = vbWhite
Me.Detail.AlternateBackColor = vbWhite
End If
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Conditional Date Format in Report for certain period
Thanks Hans, It is working well as required.
I changed the Date to Now because it was not considering current date while formatting.
Thanks for the help.
Regards,
VKKT
I changed the Date to Now because it was not considering current date while formatting.
Thanks for the help.
Regards,
VKKT
-
- 2StarLounger
- Posts: 117
- Joined: 22 Feb 2022, 09:04
Re: Conditional Date Format in Report for certain period
PMFJI,
So just use <= ?I changed the Date to Now because it was not considering current date while formatting.
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Conditional Date Format in Report for certain period
Greetings,
Hi Mr. Hans, please note that when I used a macro to pick the field datemodified, it is not working even though I have added this field to the report. Is there any changes required to make to the code you have given?
regards,
VKKT
Hi Mr. Hans, please note that when I used a macro to pick the field datemodified, it is not working even though I have added this field to the report. Is there any changes required to make to the code you have given?
regards,
VKKT
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Conditional Date Format in Report for certain period
Does it work if you change Me.DateModified to Me!DateModified ?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 13 Jun 2018, 07:50
Re: Conditional Date Format in Report for certain period
Great!! Mr. Hans, Working well.
Thanks a lot....VKKT
Thanks a lot....VKKT