Creating Report when a button is clicked with appropriate colour of each record. (1 Viewer)

Hi All

I have access table that stores details as attached. Just to give an example how data is stored , I copied data from Access table to excelsheet. Now I would like to create a report that will run when a button is clicked on the form and the vba code should display the last 7 dates from todays dates in the report along with all the data and colour the record Green,Amber and Red depending upon the Update Date and Target Date. If the Target date exceeds then that records should be coloured "Red" and if todays date equals to Date then the record should be amber and if greater than Date but less than Target Date then it should be Green.

So the report should display the last 7 dates records from todays date and the appropriate colur of each recor to let everyone know if its under target, meets target etc.

Any help would be much appreciated.

Thanks

If you go the report design and select a suitable field such as your Target Date, use the conditional formatting (under the Format menu) to set the back colour or text colour. You can create an expression in the condition as criteria to set Red/Amber/Green

David

Thanks David. I have created a report using report wizard which selected all the fields of tblupdate and then made few changes using design view. but how can i specify all the conditions in the report. I have cut and paste the Access report in excelsheet as attached . pls guide me. Thanks

do you know how to use conditional formatting in Excel? The Access CF works in a similar way.
When you say:
If the Target date exceeds then that records should be coloured "Red" and if todays date equals to Date then the record should be amber and if greater than Date but less than Target Date then it should be Green.

What exactly do you want to colour?
Also I notice you have the date field named as Date and this will cause problems when you're trying to evaluate the expression "if todays date equals to Date"

I suggest you choose an appropriate field to reflect this colour status as I don't think you can conditionally format a section of a report, you have to select a field in the detail section. You will need to set 3 formats to achieve what you want

David

Actually, I wanted to create dashboard to to show all the information discusse above. A dashboard that will show charts/graphs to let us know how many meet target(Green Colour),how many exceed target(Red Colour) Please can anyone help me in this.

Thanks