Conditional Date Format in Report for certain period

VKKT
2StarLounger
Posts: 184
Joined: 13 Jun 2018, 07:50

Conditional Date Format in Report for certain period

Post by VKKT »

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 :cheers: :cheers:

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Conditional Date Format in Report for certain period

Post by HansV »

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

VKKT
2StarLounger
Posts: 184
Joined: 13 Jun 2018, 07:50

Re: Conditional Date Format in Report for certain period

Post by VKKT »

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.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Conditional Date Format in Report for certain period

Post by HansV »

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:

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
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.
Best wishes,
Hans

VKKT
2StarLounger
Posts: 184
Joined: 13 Jun 2018, 07:50

Re: Conditional Date Format in Report for certain period

Post by VKKT »

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

Gasman
StarLounger
Posts: 81
Joined: 22 Feb 2022, 09:04

Re: Conditional Date Format in Report for certain period

Post by Gasman »

PMFJI,
I changed the Date to Now because it was not considering current date while formatting.
So just use <= ?
Using Access 2007.
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.

VKKT
2StarLounger
Posts: 184
Joined: 13 Jun 2018, 07:50

Re: Conditional Date Format in Report for certain period

Post by VKKT »

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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Conditional Date Format in Report for certain period

Post by HansV »

Does it work if you change Me.DateModified to Me!DateModified ?
Best wishes,
Hans

VKKT
2StarLounger
Posts: 184
Joined: 13 Jun 2018, 07:50

Re: Conditional Date Format in Report for certain period

Post by VKKT »

Great!! Mr. Hans, Working well.
Thanks a lot....VKKT :clapping: :clapping:
:thankyou: